Conditional Triggers - how to. 1

[Peager]

I have a number of tables with cascading relational constraints. When I update the parent table it cascades down through the whole relational tree perfectly however there is a field that is not part of the relationship that needs to be updated in the child tables ONLY if the related field changed. I have attempted this using the following code in a trigger in the parent table. There are a total of 11 child tables being updated with similar code in the complete trigger. The problem is that when enabled it's pretty obvious the trigger is not executing efficiently. I believe there are good indexes for each of the child tables but I can't seem to get decent performance out of the trigger. Can anyone point me to the error of my ways?

      UPDATE    [SOLINES]
      SET       [COMPANY] = I.[COMPANY]
               ,[Edited_View] = I.[Edited_View]
      FROM      [SOLINES] AS S
                JOIN Inserted AS I
                    ON S.[Seq_No] = I.[Seq_No]
                JOIN DELETED AS D
                    ON I.Seq_NO = D.Seq_No
      WHERE     S.[SEQ_NO] = I.[Seq_No]            -- Unique indexed key
                AND D.[COMPANY] <> I.[COMPANY]	   -- Company name was changed....

 

[bborissov]

Try:

UPDATE S -- When you use ALIAS you must use it everywhere
 SET [COMPANY]     = I.[COMPANY]
    ,[Edited_View] = I.[Edited_View]
FROM [SOLINES] S
INNER JOIN (SELECT Ins.Seq_No
                  ,Ins.COMPANY
                  ,Ins.Edited_View
            FROM Inserted Ins
            INNER JOIN DELETED Del
                      ON Ins.Seq_NO = Del.Seq_No AND
                        Del.[COMPANY] <> Ins.[COMPANY]) I
       ON S.[SEQ_NO] = I.[Seq_No]key

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Peager]

Hmmmmmm. I wouldn't have thought that adding a sub-select would improve performance. I tested your solution along with adding 'INNER' to my joins and using the Alias in the UPDATE statement and both solutions appear to be working much better. I can't tell any difference between them but I want to watch them for another day or so.

Thank you so much for your time in looking over my code.

Best regards,

Paul

 

[bborissov]

Remember SQL Server starts to parse query from smallest possible resultset.
Since INSERTED and DELETED tables can't have more than several records (you didn't insert/delete/update millions of records at a time, didn't you?) then joined both meta-tables are smallest resultset.
O!
I forgot this:

IF UPDATED([COMPANY])
   BEGIN
       UPDATE S
        SET [COMPANY]     = I.[COMPANY]
           ,[Edited_View] = I.[Edited_View]
       FROM [SOLINES] S
       INNER JOIN (SELECT Ins.Seq_No
                         ,Ins.COMPANY
                         ,Ins.Edited_View
                   FROM Inserted Ins
                   INNER JOIN DELETED Del
                             ON Ins.Seq_NO = Del.Seq_No AND
                               Del.[COMPANY] <> Ins.[COMPANY]) I
                     ON S.[SEQ_NO] = I.[Seq_No]key
     END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.