UPDATE() Inside Triggers

James Anderson shows how to use the UPDATE() function inside a trigger to operate selectively on data:

This use of the UPDATE function for selective logging can be very useful when used on tables with columns such as: LastOrderDate, LastLoginDate, etc as these columns are often updated but those changes are probably not required to be logged.

One interesting point is that even if our trigger was configured to fire on DELETEs, the UPDATE function would not return true and therefore the change would not be logged. This makes sense as a DELETE affects all columns, so checking for a particular column is not required. If we wanted to log DELETEs to our ProductPriceLog table, we would use a trigger that fired on DELETEs.

But check the comments to make sure you know when UPDATE() fires—it’s not just when a particular column changes values.

Related Posts

T-SQL Tuesday Trigger Roundup

Steve Jones has a bunch of links about triggers this month: This month was my turn to host T-SQL Tuesday. I chose┬áTrigger Headaches or Happinessas the topic, and I am glad that there have been quite a few responses. I started the review almost immediately,and here are a few highlights. I separated these based on […]

Read More

Trigger Spirals

David Fowler tells a story of woe, one which is totally not his fault: To do this, a trigger was created which would send all the details via a Service Broker message to another SQL Server, this SQL Server was used to hold details of the AD accounts and from there, changes were automatically propagated […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930