Here’s an example of using OUTPUT to simplify the recording of updates etc …

-- create the audit log
create table audit_table (fname varchar(50), colour varchar(50), 
old_tasty int, changed smalldatetime, new_tasty int)

-- create and populate the fruit table
create table fruit (fname varchar(50),colour varchar(50),tasty int)
insert into fruit values ('banana','yellow',6),('kiwi','green',3),('gooseberry','green',2)

-- change score to 'yuk' for any green fruits
update fruit set tasty = 1
output deleted.*, getdate(), inserted.tasty into audit_table
where colour = 'green'

-- check audit log
select * from audit_table

… and another nail in the coffin of Triggers 🙂

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