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
IFEXISTS (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]
FROMtblUsers 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,
