When IF UPDATE(colName) does not work for you in an After Update Trigger


I have come a question about how to update a timstamp column based on the change from another column only, not the overall change of the row. When you use UPDATE(), it will update each time there is any change attempt for the row. It will not meet the requirement of this question. Here I come up with some T-SQL code inside the trigger to do this:

ALTER trigger [dbo].[UPDATE_Supplemental_record]
on [dbo].[supplemental_recordDetail]
after update
as
BEGIN

–IF NOT UPDATE(DirectorApprove)
–RETURN

if not exists (select * FROM deleted d inner join supplemental_recordDetail s
on d.id=s.id AND (d.DirectorApprove=s.DirectorApprove
Or (d.DirectorApprove IS NULL AND s.DirectorApprove is NULL)))

update [supplemental_recordDetail]
set DirectorApproveTime =
CASE WHEN DirectorApprove >0
THEN GETDATE() ELSE null END

END

http://msdn.microsoft.com/en-us/library/ms187326(v=sql.90).aspx
http://forums.asp.net/t/1776423.aspx/1?How+to+conditionally+update+one+field+using+trigger+in+SQL+server+2000

“Deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. ”
http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s