Making the (Almost) Generic Data Audit Trail even more generic

Yesterday I posted an entry showing how to make an (Almost) Generic Data Audit Trail for SQL Server. I stated that “the trigger itself does not know what table it is operating on” – this is in fact wrong, as a colleague pointed me to this posting by Erland Sommarskog.

What needs to be done to change the trigger into being even more generic is this:

/*————————————————————-*/
/* (Almost) Generic Data Audit Trail, Per Hejndorf 2007                                                 */
/*————————————————————-*/

CREATE TRIGGER [dbo].[trigMyTable] ON [dbo].[MyTable]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @TableName VARCHAR(50);
SELECT @TableName = OBJECT_NAME(PARENT_OBJ) FROM SYSOBJECTS WHERE id = @@PROCID

DECLARE @count_inserted int, @count_deleted int;
SELECT @count_inserted = COUNT(*) FROM inserted;
SELECT @count_deleted = COUNT(*) FROM deleted;
DECLARE @xml_inserted xml, @xml_deleted xml;
SELECT @xml_inserted = (SELECT * FROM inserted FOR XML RAW);
SELECT @xml_deleted = (SELECT * FROM deleted FOR XML RAW);

INSERT INTO Audit VALUES (GetDate(), SYSTEM_USER, @TableName, @count_deleted, @xml_deleted, @count_inserted, @xml_inserted);

Now it would have been really nice to put the whole thing in a stored procedure, but unforunately this is not possible as knowledge of the “inserted” and “deleted” tables are not passed on tho the stored produre. If reuse of the trigger-body would be possible using a CLR based trigger is something that could be looked into.

Advertisements
Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s