SQLTeam.com | Weblogs | Forums

UPDATE([column_name]) in an INSERT trigger


#1

I have a trigger on a table...

AFTER UPDATE, INSERT
.
.
.
if update([status])
begin
.
,
do something
.
.
end

The question is.. On an INSERT, will the above "if update([status])" be true or false?


#2

It will be TRUE if the column was included in the statement. It has nothing to do with whether the value (of that column) has changed. Sorry ... that was probably what you wanted (me too!)

Note that the Trigger will fire on ALL rows as a single set and NOT as individual rows, so you would need to do something like:

... some operation ...
FROM inserted AS I
    LEFT OUTER JOIN deleted AS D
        ON D.MyPKey = I.MyPKey
WHERE     I.[status] <> D.[status]
      OR (I.[status] IS NULL AND D.[status] IS NOT NULL)
      OR (I.[status] IS NOT NULL AND D.[status] IS NULL)

#3

For INSERT, UPDATE() should always be true for every column.