SQL Server Triggers – Which fields have been updated? – Part 2

In the last post (SQL Server Triggers – Which fields have been updated? – Part 1) we explored various methods to determine which columns in a table were being updated.  All those methods relied on a SQL trigger being fired.  Once fired, if a specific column in the table was updated we could carry out an appropriate action, such as inserting an audit record into another table.  However, what if the UPDATE statement was executed against the table, but the actual values of the fields were not changed?  Consider the following example:

CREATE TABLE MyTable (Col1 INT IDENTITY(1,1), Col2 varchar(10), Col3 INT)

GO

CREATE TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       PRINT 'An Update has occurred'

END

GO

INSERT INTO MyTable

SELECT 'a',7 UNION ALL

SELECT 'b',8 UNION ALL

SELECT 'c',9 UNION ALL

SELECT 'd',10

GO

UPDATE MyTable

SET Col3 = 10 WHERE Col2 = 'd'

The UPDATE statement above has overwritten Col3 with the same value as it had before.  The table data has not actually been changed.  Nonetheless, because an UPDATE statement has been executed, the AFTER UPDATE Trigger is fired and will output the print statement.  In fact even if the UPDATE statement had not affected a single row, the trigger would still have been fired, outputting the print statement.

This is often not the desired behaviour – I don’t want to be inserting audit records if nothing has actually changed.  Depending on what logic has been implemented in the trigger and how often it is fired, this could have an adverse impact on performance.  Therefore the first improvement we can make is to ensure nothing happens if no records have been updated.  To do this we use the @@ROWCOUNT global variable (for more on this variable see my post on SQL Server Global Variables):

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       IF @@ROWCOUNT = 0 RETURN;

       PRINT 'An Update has occurred'

       --Perform UPDATE logic here

END

GO

This trigger will now immediately return, doing nothing, if no records have been updated.

Next we need ensure we only take action if field values have actually changed.  To do this we can make use of the special inserted and deleted tables.  These are temporary tables created and managed automatically by SQL Server.  While we cannot perform any DDL operations on these tables, they contain identical columns to the original table the trigger is created on, and we can refer directly to these columns in the code.  When a DML trigger fires, these two tables are populated with the deleted and inserted records from the underlying table respectively.  If records are updated then the original pre-updated record arrives in the deleted table and the post-updated record in the inserted table.  Therefore, if we want to know if previous values have actually been updated with new, different values we can compare the contents of these two tables:

ALTER TRIGGER dbo.tr_MyTable ON dbo.MyTable  AFTER UPDATE

AS

BEGIN

       IF @@ROWCOUNT = 0 RETURN;

       IF EXISTS(    SELECT *

                           FROM          inserted i

                           INNER JOIN    deleted d

                                   ON    i.Col1 = d.Col1

                           WHERE         d.col2  <> i.col2

                                  OR     d.col3 <> i.col3           

                     )            

       BEGIN        

              PRINT 'An Update has occurred'

       END

END

In the above trigger, inside the IF EXISTS function, we are joining the inserted and deleted tables together on Col1, the identity column.  Because these tables are effectively the “before” and “after” state of the data, we are only interested where the values in the remaining fields do not match, indicating that data has changed in those fields.  This is logic is carried out by the WHERE clause.  Only if such rows exist do we then perform the desired UPDATE logic, in this case the PRINT statement.  

The above trigger will now only do anything if data has actually changed in the underlying table.  This has the potential of saving a lot of time and unnecessary processing of data on those occasions when an UPDATE has occurred but no overall change has actually taken place.