COmpare inserted vs deleted triggers

• timscronin

  SSCertifiable

  Points: 6851

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  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)
  Intro to Tally Tables and Functions