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

you could do this with an ALTER TABLE SQL command, however it will fail if the contents of your text or ntext fields exceeds the capacity of varchar(max) > 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