Wednesday, March 21, 2012

How to create an Audit Table

Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry
Here is my trigger:
Create TRIGGER tg_audit_task_order_awardees on xxx
for INSERT,UPDATE,DELETE as


INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)

Select
'OLD',
del.to_awardee,
del.solicitation_id,
del.contract_id,
del.order_number,
del.amount,
del.show_public,
getdate(),
del.modified_user
FROM deleted del


/* for a new record */

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)
Select
'NEW',
ins.to_awardee,
ins.solicitation_id,
ins.contract_id,
ins.order_number,
ins.amount,
ins.show_public,
getdate(),
ins.modified_user
FROM inserted ins

Take a look at this article... offcourse there are other ways to do it, but this might give you some ideas to do it... I believe the only problem is it can't handle text and ntext fields:http://www.codeproject.com/database/AuditTriggers.asp

No comments:

Post a Comment