CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
STORM
STORM

Reputation: 4341

How to check in UPDATE Trigger if record has really changed

I have a SQL Server 2012 UPDATE Trigger which occurs when data is updated within a table in the database (Microsoft Project Server Database) :)

I am using the following statement to get the updated element from the table within the Trigger

SELECT @dataId = INSERTED.Id FROM INSERTED;

But the problem is due to the behaviour of the Microsoft Project Server, that it updates ALL Tasks within a Project even if only one task was changed (each Task is one record in database).

I know that i can get the id of the item which should be updated, but i dont know how i could compare the to updated row with the data which is still in the database within the UPDATE Trigger?

Upvotes: 1

Views: 1739

Answers (1)

gbn
gbn

Reputation: 432657

To get what rows actually changed, then use this inside the trigger

SELECT 
    * 
FROM 
  INSERTED I
  JOIN
  DELETED D ON I.KeyCol = D.KeyCol
WHERE
  NOT EXISTS (
     SELECT I.Col1, I.Col2, I.Col3, I.Col4 ...
     INTERSECT
     SELECT D.Col1, D.Col2, D.Col3, D.Col4 ...
     )

The NOT EXISTS..INTERSECT deals with NULLs etc for you already

Upvotes: 3

Related Questions