Pete, This is awesome! I'm going to consider using it for certain tables that we have problems with. I might suggest that you add in the user name and/or ip that made the change. That can help if you need to find out who made specific changes!
Thanks, Steve -----Original Message----- From: Pete Ruckelshaus [mailto:pruckelsh...@gmail.com] Sent: Friday, July 30, 2010 10:20 AM To: cf-talk Subject: SOT: SQL Server Data Archival - my solution I know a lot of time people (including myself) come here with questions, and don't have many opportunities to offer solutions to share. I had a need to store a record of changes to records in database tables for a content management system. I wanted to keep things simple, without requiring a bunch of additional code to my CF codebase, and I didn't want to make SQL Server management more complicated than it needed to be by adding a ton of tables. Anyway, in a nutshell, I've got an archive table that stores changed records as an XML object in a field. An insert, update, delete trigger grabs values from the inserted or deleted tables, packs that info up, and saves it to the archive table. The beauty of this is its relative simplicity -- the archive table can store data from any table in the database. Here's the write-up. I'm sure it's not perfect -- I'm far from an SQL Server expert - but it's going to work very well for my needs. Process - single archive table storing XML The archive table would look something like this: *tblArchive* uid uniqueidentifier DEFAULT NEWID(), timestamp datetime DEFAULT GETDATE(), sourceTable varchar(100), sourceID int, action varchar(10), xData xml * UID would be a unique identifier for this archive table. * Timestamp would set the time of archive and would allow to sort revisions based upon when they happened. * sourceTable would be the name of the table that the data is coming from. * sourceID would be the record identifier (presumably the primary key) of that record from the original table. * Action would be whatever SQL action was performed on that record, i.e. insert, update, delete * xData would be the XML object that stores the record that is being changed. Assuming a fictitious (and rather simple) table named "tblUsers" with the following structure: *tblUsers* id (int, PK, ident, autoincrement), fname (varchar(20)), lname (varchar(30)), employeeid (int), extension (int) Archive Trigger for Inserts, Updates and Deletes For each table that is to have archived data, run the following trigger, modifying table names where necessary: CREATE TRIGGER [tgrArchiveRecord] ON [tblUsers] FOR INSERT, UPDATE, DELETE AS IF @@rowcount = 0 RETURN DECLARE @table varchar(100); DECLARE @sourceid int; DECLARE @action varchar(10); SET @table = 'tblUsers'; IF EXISTS (SELECT * FROM DELETED) BEGIN IF EXISTS (SELECT * FROM DELETED) AND EXISTS (SELECT * FROM INSERTED) BEGIN SET @sourceid = (SELECT id FROM inserted); SET @action = 'update'; INSERT INTO tblArchive(sourceTable, sourceid, action, xData) SELECT @table, id, @action, (SELECT * FROM deleted AS record WHERE deleted.id = record.id FOR XML AUTO) FROM deleted; RETURN END SET @sourceid = (SELECT id FROM deleted); SET @action = 'delete'; INSERT INTO tblArchive (sourceTable, sourceid, action, xData) SELECT @table, id, @action, (SELECT * FROM deleted AS record WHERE deleted.id = record.id FOR XML AUTO) FROM deleted; END ELSE BEGIN SET @sourceid = (SELECT id FROM inserted); SET @action = 'insert'; INSERT INTO tblArchive (sourceTable, sourceid, action, xData) SELECT @table, id, @action, (SELECT * FROM inserted AS record WHERE inserted.id = record.id FOR XML AUTO) FROM inserted; END GO Retrieving Data From Archive The likely scenario for retrieving data from the archive table is to either display a history of a record, or to present previous changes so that a user can roll back changes to a previous version. Retrieving data from the Archive table's XML column is fairly straightforward: SELECT NULL as uid, u.id AS id, u.fname, u.lname, u.employeeid, u.extension, getdate() AS [timestamp] FROM tblUsers u UNION SELECT A.uid, A.sourceID AS id, A.xData.value('(/*/@fname)[1]', 'varchar(20)') AS fname, A.xData.value('(/*/@lname)[1]', 'varchar(30)') AS lname, A.xData.value('(/*/@employeeid)[1]', 'int') AS employeeid, A.xData.value('(/*/@extension)[1]', 'int') AS extension, A.timestamp AS [timestamp] FROM tblArchive A WHERE A.sourceTable = 'tblUsers' ORDER BY [timestamp] desc Creating a view that returns data in this manner for each table that has data being archived would be very useful: CREATE VIEW vwUsersWithArchive AS SELECT NULL as uid, u.id AS id, u.fname, u.lname, u.employeeid, u.extension, getdate() AS [timestamp] FROM tblUsers u UNION SELECT A.uid, A.sourceID AS id, A.xData.value('(/*/@fname)[1]', 'varchar(20)') AS fname, A.xData.value('(/*/@lname)[1]', 'varchar(30)') AS lname, A.xData.value('(/*/@employeeid)[1]', 'int') AS employeeid, A.xData.value('(/*/@extension)[1]', 'int') AS extension, A.timestamp AS [timestamp] FROM tblArchive A WHERE A.sourceTable = 'tblUsers' "Gotcha's" As far as issues when using this approach, there is one primary one - you cannot use text, ntext, or image data types with insert, update, or delete triggers. The solution is to convert any text, ntext, or image data types (which will apparently be deprecated in the future, anyway) into varchar(MAX) or varbinary(MAX) data types. Varchar(MAX) supports 2GB of data per field, which is the same as text and ntext. To determine which fields in a given database are of the text, ntext, or image data types, run the following query against the database in question: select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' OR DATA_TYPE = 'ntext' OR DATA_TYPE = 'image' order by table_name Unfortunately, as updates to system tables are not allowed (and are generally a bad idea), there's no quick and easy way to convert text or ntext columns to varchar ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335885 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
