CODEWITHSUNDEEP
sqlsql-server-2008triggers
dotnetnoob
dotnetnoob

Reputation: 11330

How to combine triggers

SQL newbie looking for help.

I'm using SQL Server 2008.

At the moment I have 2 triggers, which both relate to the same table.

Is is possible to combine these 2 triggers or is it better practice to keep them separate?

ALTER TRIGGER PingtreeNode2_IU_Trig
ON dbo.PingtreeNode
FOR INSERT, UPDATE
AS
IF NOT EXISTS (
                SELECT
                        pt.ID
                    FROM
                        PingtreeTier pt
                    INNER JOIN
                        inserted i ON
                            pt.ID = i.PingtreeTierID
                    INNER JOIN
                        Product p ON
                            i.ProductID = p.ID
                    WHERE
                        pt.ProductServiceTypeID = p.ServiceTypeID
                )
BEGIN
    ROLLBACK TRANSACTION
END

ALTER TRIGGER PingtreeNode_IU_Trig
ON dbo.PingtreeNode
FOR INSERT, UPDATE
AS
IF NOT EXISTS (
                SELECT
                        pt.ID
                    FROM
                        Pingtree pt
                    INNER JOIN
                        PingtreeTier pt2 ON
                            pt.ID = pt2.PingtreeID
                    INNER JOIN
                        inserted i ON
                            pt2.ID = i.PingtreeTierID
                    INNER JOIN
                        Product p ON
                            i.ProductID = p.ID
                    WHERE
                        pt.ProductTypeID = p.TypeID
                )
BEGIN
    ROLLBACK TRANSACTION
END

Upvotes: 0

Views: 609

Answers (1)

M.Ali
M.Ali

Reputation: 69524

ALTER TRIGGER PingtreeNode2_IU_Trig_Combined
ON dbo.PingtreeNode
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;
IF(   
   (
   NOT EXISTS (
               SELECT pt.ID
               FROM PingtreeTier pt INNER JOIN inserted i 
               ON   pt.ID = i.PingtreeTierID
               INNER JOIN Product p 
               ON i.ProductID = p.ID
               WHERE pt.ProductServiceTypeID = p.ServiceTypeID 
               ) 
    )          
   OR 
    (
   NOT EXISTS (
                SELECT pt.ID
                FROM Pingtree pt INNER JOIN PingtreeTier pt2 
                ON pt.ID = pt2.PingtreeID
                INNER JOIN inserted i 
                ON  pt2.ID = i.PingtreeTierID
                INNER JOIN Product p 
                ON i.ProductID = p.ID
                WHERE  pt.ProductTypeID = p.TypeID
                )
     )
  )

    BEGIN
        ROLLBACK TRANSACTION
    END
END

Upvotes: 2

Related Questions