Audit Tables and triggers

[Jeff Magouirk]

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

[Erland Sommarskog]

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_reaso n", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp