Writing Audit Triggers 2

[LeonelSanchezJr]

I need the code / syntax to write an Audit Trigger which will basically do the following:

When a record gets inserted into a table or updated, that one record needs to be written to an audit table.

 

[rajeevnandanmishra]

Hi Leonelsanchezjr,
Do as following:
1. Create a table with same structure as of the original one.
2. Create a Trigger as following:
CREATE TRIGGER insupdMainTable
on MainTable for insert, update as
INSERT myAuditTable
SELECT * FROM inserted

Now whenever you are doing any insert/ update the new row will automatically be inserted in the audittable.

Hope it works!!!

 

[jjonesal]

I just came up with this off the top of my head, so I haven't tested it, but I think this will work. Below is a sample of an update trigger (assume table1 is your original table name & audittbl is your new audit table, and field names in table1 are field1, field2, etc...)

You should be able to use the inserted and deleted logical tables to pull the values that have changed or the new updated values to use for inserting into the audit table.

Note, this trigger only fires on update, but the insert would be identical (only you wouldn't attempt to write the "before" image or check to see if columns were updated.)

CREATE TRIGGER updrec
ON table1

FOR UPDATE AS

IF (COLUMNS_UPDATED() & 14) > 0

BEGIN

-- insert before image into audit table
INSERT INTO audittbl
(audit_typerec, audit_field1,audit_field2,...)
SELECT 'OLD', del.audit_field1, del.audit_field2,...
FROM deleted del

-- insert after image into audit table
INSERT INTO audittbl
(audit_typerec, audit_field1, audit_field2, ...)
SELECT 'NEW', ins.audit_field1, ins.audit_field2, ...
FROM inserted ins

END


There might be a better way to do this, but this is what comes to mind for me....

J. Jones
jjones@cybrtyme.com

 

[jjonesal]

My previous post would allow you to see a before & after image, so you know what exact changes were made with each update.... J. Jones
jjones@cybrtyme.com