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,

JeffJeff Magouirk (magouirkj@.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, esquel@.sommarskog.se

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