I've created Delete, Insert, and Update triggers for a few of our core tables to log the changes as a single XML field to a Log table, which is working great, but I'm not sure how to bring all that data back together to generate a report of changes to the table.
Here's some SQL showing an example of how I set it up for the Delete trigger:
[Code="SQL"]
CREATE TABLE Accounting (ID NUMERIC(18,0) IDENTITY NOT NULL, NAME NVARCHAR(100))
GO
CREATE TABLE LogData (
NVarchar(100), [Modification] NVARCHAR(10),RowsAffected NUMERIC(18,0), [Data] XML)
GO
Create TRIGGER tgrAccountingDelete
ON Accounting FOR DELETE AS
INSERT INTO [LogData] (
[Table],
[Modification],
RowsAffected,
[Data])
SELECT 'Accounting',
'Delete',
(SELECT COUNT(*) FROM DELETED),
(SELECT * FROM Deleted FOR XML AUTO, ELEMENTS)
GO
INSERT INTO Accounting (NAME) VALUES ('Test1'),('Test2'),('Test3')
GO
DELETE FROM Accounting WHERE ID = 1
DELETE FROM Accounting
Go
SELECT * FROM LogData
Go
DROP TABLE LogData
DROP TABLE Accounting
GO
[/code]
LogData table contains this:
TABLE Modification RowsAffected Data
Accounting Delete 1 <Deleted><ID>1</ID><NAME>Test1</NAME></Deleted>
Accounting Delete 2 <Deleted><ID>3</ID><NAME>Test3</NAME></Deleted><Deleted><ID>2</ID><NAME>Test2</NAME></Deleted>
Is it possible to write a process to parse the Data column and make it selectable or possibly present the data in a report so we can see what changes have been made? This is a very watered down version of the process I have in place, what I have also brings in the username and more info on who changed the record.
Thanks for any suggestions --
Sam