October 4, 2010 at 1:54 pm
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:
TABLEModificationRowsAffectedData
AccountingDelete1<Deleted><ID>1</ID><NAME>Test1</NAME></Deleted>
AccountingDelete2<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
October 5, 2010 at 11:31 am
Perhaps this post would be of help?
http://www.sqlservercentral.com/Forums/Topic997589-21-1.aspx
It was a question I posted in the XML forum last week. I needed to extract a piece of data from my XML fragment based on the value in another column in my error table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply