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:335884
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
