SQL triggers – avoiding recursion

SQL Triggers – Avoiding recursion

I have run into situations in SQL where it may be necessary to update a field in a table, based on the data in another field of the same table.  While it may be possible to make the second field a computed field, there at times that it is much less convoluted to simply update the table again, from within the trigger.

That seems simple enough, just do an update statement inside your trigger.  However, if you do this in an update trigger, and update the table the trigger is on, it will fire the trigger again.  Then the update statement will execute again, and re-fire the trigger.  In other words, you are stuck in a recursion loop.  That loop will run, until you run out of resources somewhere, and then fail.

It is possible to update a table based on its own trigger, however, if you add a little bit of conditional control.  The secret here is to use the "IF UPDATE()" function.  This tests to see if the field specified was updated or not.  

A simple example would be a trigger on TABLE_A, that sets FIELD_B, only when FIELD_A was changed.  For our purposes, assume you have an UPDATE trigger on TABLE_A.  Inside that trigger, you would have the code:

     IF UPDATE(FIELD_A)
     BEGIN
          UPDATE TABLE_A set FIELD_B=
     END

So, if FIELD_A was changed during the transaction that fired the UPDATE trigger, then the "UPDATE TABLE_A …" command will be executed.  This will cause the UPDATE trigger on TABLE_A to fire again.  However, the transaction that fired the trigger did not change FIELD_A, only FIELD_B.  So, "UPDATE(FIELD_A)" will be false this second time through, and the update command will be skipped.

That is one, simple, use for the "UPDATE()" function.  I have also used it to track changes to a specific field, and log who made the changes, and when they occurred, when only one field was of interest.  For example one field in the item record was to be tracked.  By testing for that specific field changing, there was not log entry generated when for example, the quantity changed due to a sale.

Dave.