August 31, 2007 at 7:33 am
Guys,
We looking to audit tables in the database. In the tables auditing needs to be done only on few columns not all the columns. Is there any 3rd party tool available to do it can it auditing be done on columns only with sql server 2005. Trigger doesnt work in this scenario as it works per row it does not say which column in the row was updated/inserted/deleted.
For example in the below employee table, I want to audit(history) only ssn and dob columns - all the updates/inserts/deletes made to this column should be audited.
empid empname ssn dob
1 John,smith 122222222 07/29/2001
Any suggestions and inputs would help
August 31, 2007 at 7:49 am
I think you can use a trigger. If there's an insert or delete, you'll want to audit that, because a whole row will have been affected. If there's an update, you can compare what was inserted with what was deleted, and copy into your audit table if ssn or dob were affected.
Have a go and post back if you're having trouble writing the trigger.
John
August 31, 2007 at 7:52 am
Why not use the following in a trigger?
create trigger <name>
after Insert, Update
as
If update(Your Columm)
begin -- do your auditing
end
else
return
Then you can audit only for those columns you are interested in. Hope this helps
S
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply