5

If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger?

I know we can do a comparison of each individual field (handling the ISNULL side as well), but where it's a table that has 50+ fields, is there a faster/easier way to do it?

Note: This could be used to reduce IO/noise in audit records etc...

EXAMPLE

For a table of:

CREATE TABLE myTest
(ID int NOT NULL IDENTITY(1,1), 
 Field1 varchar(10) NULL,
 Field2 varchar(20) NULL)

with an after updater trigger containing:

INSERT INTO myTestAudit (ID, Field1, Field2, DateTimeUpdate)
SELECT ID, Field1, Field2, getDate()
FROM inserted

with initial values:

INSERT INTO myTest (Field1, Field2)
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c'

Now run an update:

UPDATE myTest set Field2 = 'b' WHERE Field1 = 'a'
Improve this question
2
  • I have two questions: Why do you want to ignore updates that don't modify anything, and more importantly why are there updates being performeed that don't modify anything? Sound like if you filter out this data, you may be ignoring a serious problem with the application.
    – datagod
    Jan 30, 2012 at 18:18
  • 1
    There are indeed problems in the application(s) where updates are being called with no changes, this creates noise on the server and does need to be cleaned up (issue has been created and queued), however the audit triggers are creating extra noise by writing them those changes as well...
    – Andrew Bickerton
    Jan 31, 2012 at 9:06

2 Answers 2

Reset to default
5

To bring back rows where at least one value has changed you can use

SELECT /*TODO: Column List*/
FROM   INSERTED I
       JOIN DELETED D
         ON I.ID = D.ID
            AND EXISTS (SELECT I.*
                        EXCEPT
                        SELECT D.*)
Improve this answer
4
  • +1 interesting, do you have any info on performance and how it handles nullable values?
    – Andrew Bickerton
    Jan 31, 2012 at 9:10
  • d'oh! found answer on SO (stackoverflow.com/questions/1662902/…) that reports EXCEPT treats NULL values as matching, which would not work...
    – Andrew Bickerton
    Jan 31, 2012 at 9:21
  • 1
    @AndrewBickerton - Nope. This is what you need. If both sides are (1,NULL) for example then you want null values to be treated as equal and (1,NULL) EXCEPT (1,NULL) to return an empty set as nothing has changed. Some info here
    – Martin Smith
    Jan 31, 2012 at 10:28
  • you're right (hadn't had my morning coffee!)
    – Andrew Bickerton
    Jan 31, 2012 at 10:43
2

Put this as the very first line in your trigger:

-- Exit trigger if no data was actually modified
IF (@@ROWCOUNT  = 0)  return

I do this in all of my audit triggers. It is short and to the point, definitely cuts down on wasted I/O.

Update: Sorry, I misread your question. I thought you asked how to exit the trigger if no data has changed.

Improve this answer
5
  • we do this already (and yes it definitely helps!).. Good advice though.
    – Andrew Bickerton
    Jan 30, 2012 at 14:09
  • 1
    This isn't always reliable. I can't quantify when, but I have been bitten. I use IF NOT EXISTS (SELECT * FROM INSERTED) RETURN
    – gbn
    Jan 30, 2012 at 15:55
  • 1
    @gbn - For a MERGE statement @@ROWCOUNT contains the row count of all actions - Not just the one being handled by the specific trigger that is currently firing. Was that it?
    – Martin Smith
    Jan 30, 2012 at 17:43
  • @MartinSmith: no, before MERGE on '2000 and '2005.
    – gbn
    Jan 30, 2012 at 17:44
  • In my case, we needed to exit the trigger as absolutely fast as possible. The audit was a "nice to have" feature, but performance was still top priority (very busy server). I wonder if there is some SQL bug somewhere that causes @@ROWCOUNT to not be properly incremented.
    – datagod
    Jan 30, 2012 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.