December 7, 2005 at 2:57 pm
I have a table that I wish to put a trigger on that will get the before value of a column, the new value and then insert an audit record. I can't seem to get this to work with an AFTER trigger, because the before column value is already been changed. I've tried an INSTEAD OF trigger, this allows me to get the before and after value, but doesn't actually perform the update to the table.
Is there a way to do this?
Thanks, in advance
Aaron
December 7, 2005 at 3:21 pm
Read this from books online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_0lo3.asp
When an after trigger exists, then sql server uses virtual tables called inserted and Deleted,
Inserted are the values you want to update to, deleted are the values before the update.
Like this
create trigger tr_t_MyTable_audit
on t_MyTable
after update as
insert t_MyTable_Audit
(a_pk, a_bdrqID, a_Offer_Expiration, a_Full_Payment_Date,
a_Status, a_SurrogateID, a_Type, a_Active, a_Created_By,
a_Last_Updated_By, a_Record_Created, a_Record_Last_Updated, bdrsa_Action)
select
pk, bdrqID, Offer_Expiration, Full_Payment_Date,
Status, SurrogateID, Type, Active, Created_By,
Last_Updated_By, Record_Created, Record_Last_Updated, 'u'
from deleted d
December 7, 2005 at 3:27 pm
Thank you SO much for your help! That worked. I wont forget this information now.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply