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
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.