SQL Server Data Archival - my solution

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

Steve, to keep things simple(r), I would store that info at the source table level.  Since it's a web site, there's a single generic user account at the database level, but I do track user and IP at the CF application level in the table that specific data is stored...it's just not shown in the tblUsers example table. Pete On Fri, Jul 30, 2010 at 10:26 AM, DURETTE, STEVEN J (ATTASIAIT) < sd1985@att.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

Very nice! Thanks for sharing that. Think I'll play with it a bit as well. Robert B. Harrison Director of Interactive Services Austin & Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or.  It must be &. Plug in to our blog: A&W Unplugged http://www.austin-williams.com/unplugged __________ Information from ESET Smart Security, version of virus signature database 5326 (20100730) __________ The message was checked by ESET Smart Security. http://www.eset.com

Feel free to pass on any enhancements or improvements! On Fri, Jul 30, 2010 at 11:02 AM, Robert Harrison < robert@austin-williams.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I find this intriguing as well. Almost like a poor mans historical archive system. A generator for the triggers would be cool also based on Illidium PU-36 -------------- Dan O'Keefe > > Feel free to pass on any enhancements or improvements!