469,578 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

Audit Tables and triggers

Dear Group,

I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.

Say I have a table with

Subject_ID, visit_number, dob, weight, height, User_name, inputdate

The audit table would have .

Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.

Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.

Help with this would be great, since I am new to the world of triggers.

Thanks,

Jeff
Jul 23 '05 #1
1 1779
Jeff Magouirk (ma*******@njc.org) writes:
I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.

Say I have a table with

Subject_ID, visit_number, dob, weight, height, User_name,
inputdate

The audit table would have .

Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.

Where the edit_action would be insert, update, delete; the edit_reason
would be the reason given for the edit.

Help with this would be great, since I am new to the world of triggers.


If you need to do to this on a broad scale, consider 3rd-party solutions.
Two that I usually recommend - although I've used none of them myself -
is SQLAudit from Red Matrix and Entegra from Lumigent. SQL Audit is
based on triggers, Entegra works from the transaction log.

But for a one-shot you could do:

CREATE TRIGGER tbl_audit_tri ON tbl FOR INSERT, UPDATE, DELETE

IF @@rowcount = 0
RETURN

IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ....
CASE WHEN EXISTS (SELECT * FROM deleted)
THEN 'UPDATE'
ELSE 'INSERT'
FROM inserted
END
ELSE
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ...., 'DELETE'
FROM delete
END

As you see I have left out edit_reason. This is because I don't know
what you mean with "edit_reason", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Keith | last post: by
2 posts views Thread by Zlatko Matić | last post: by
3 posts views Thread by Zlatko Matić | last post: by
6 posts views Thread by Parag | last post: by
2 posts views Thread by Scott Cain | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.