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.
