Post reply

COmpare inserted vs deleted triggers

  • January 24, 2008 at 4:03 pm

    #71350

    I have a trigger on a table for update where the update may be a record at a time or a mass update. I need to check if one of the columns has changed (not updated). I can't use variables to trap values because with a mass update I leaned my lesson the it only updated the first record. The question is how can I compare it. I tried below but this errors out

    if select ltrim(rtrim(inserted.df_sitename)) from inserted <> deleted.df_sitename

    begin

    It wants me to reference the tables in some type of where clause

  • January 24, 2008 at 8:37 pm

    #771573

    What is it that you want to do if there a rows that have changed between Inserted and Deleted? Perhaps insert into an audit log?

    Then, ya gotta think in sets instead of IF's...

    INSERT INTO AuditLog

    (PK,OldValue,NewValue,ColumnName)

    SELECT PK_Col AS PK,

    d.df_SiteName AS OldValue,

    i.df_SiteName AS NewValue,

    'df_SiteName' AS ColumnName

    FROM Inserted i

    INNER JOIN Deleted d

    ON i.PK_Col = d.PK_Col

    AND ISNULL(i.df_SiteName,'') <> ISNULL(d.df_SiteName,'')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply