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 
