My Technical Notes

Thursday, 13 May 2010

Deletion Auditing Trigger in SQL Server

To audit deletions of rows in a SQL Server database, you can use a trigger to log the xml of the table row to a deleted rows table:


CREATE TRIGGER Project_Delete ON Project FOR DELETE AS
 declare  @rowXml xml
 set @rowXml = (SELECT * FROM Deleted FOR XML AUTO, TYPE, ELEMENTS)
 INSERT INTO DeletedRow(RowXml)
 values(@rowXml)
GO

No comments: