Using Update Triggers in SQL Server

I spent hours searching through Books on Line to learn how to use triggers and didn’t find what I needed there. It may be there, somewhere, in the cryptic language Microsoft likes to couch things in but it certainly wasn’t apparent to me.
I wanted to write a trigger that audited a specific action on a column in a table and I needed to know the previous and updated entry for this column. What BOL didn’t tell me was that the ‘deleted’ variable held the previous value although it did tell me about the ‘inserted’ value which is the new value. This, I discovered in an old SQL Team Introductory article on Triggers by Garth. So it works something like this:

create trigger tr_testtable_mycolumn_update
on tbltesttable
for UPDATE
as
declare
@prevint int,
@postint int
select @prevint=mycolumn from deleted
select @postint=mycolumn from inserted
… then do stuff like update something in an audit table using @prevint and @postint …
GO

Anyway. Garth’s article helped me to solve my problem, thanks buddy! There are other sources on the net for this kind of information, such as SQLServerCentral.

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , . Bookmark the permalink.

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