July 9, 2003 at 10:57 am
Our client has an interesting request. We have a table containing multiple records. ?The requirement is, whenever a record is updated:
(1) A trigger checks to see if the column modified is listed in another table that lists columns of interest.
(2) If that column is of interest, the value of the column (before updating) is archived to an archive table.
(3) Once a month, the archived columns are moved from the archive table to a history table.
The part we haven't quite figured out is how to cache the value of a column that has been updated and write that after the update takes place.
Another complication is that the client is on SQL 7 with no plans to update to SQL 2000
Any ideas would be greatly appreciated!
====================
"Awful tired now, Boss. Dog tired."
====================
"Awful tired now, Boss. Dog tired."
http://www.dwacon.com
July 10, 2003 at 6:12 am
1. Trigger for tracking changes
Yes, you can use update trigger to track the changed column values.
The old and new values will be available in deleted and inserted tables respectively.
I have created a sample script which does the similar jop. The details of script as below
TestTrigger - This is the table on which changes need to be tracked
tu_TestTrigger - This is trigger which tracks the changes
ColumnsOfInterest - This table contains the columns of interest
AuditChanges - This table contains the old and new values for each column after update
2. Sql*Server 7.0
I have created this script in sql*2000. However the same should work on version 7 also as
I have not used any special features of 2000.
3. Regarding your other query on caching changes and writing after update done does not seem
necessary. This is because if the update fails the data written from trigger also will be
deleted automatically (implicit rollback).
SET NOCOUNT ON
GO
Drop Table TestTrigger
GO
Create Table TestTrigger
(
code int PRIMARY KEY,
name varchar(10),
profession varchar(20),
dummy varchar(20)
)
GO
insert into TestTrigger values(1,'Emp1','profession1','dummy1')
insert into TestTrigger values(2,'Emp2','profession2','dummy3')
GO
DROP Table ColumnsOfInterest
GO
CREATE Table ColumnsOfInterest
(name varchar(20) Primary key)
GO
INSERT INTO ColumnsOfInterest Values ('name')
INSERT INTO ColumnsOfInterest Values ('profession')
GO
Drop Table AuditChanges
GO
Create Table AuditChanges
(
code int,
colname varchar(20),
oldvalue varchar(255),
newvalue varchar(255),
ts timestamp
)
GO
Create Trigger tu_TestTrigger
ON TestTrigger
FOR UPDATE
AS
BEGIN
INSERT INTO AuditChanges(code,colname,oldvalue,newvalue)
SELECT i.code,sc.name, (CASE sc.name
WHEN 'code' THEN CAST(d.code AS VARCHAR)
WHEN 'name' THEN d.name
WHEN 'profession' THEN d.profession
WHEN 'dummy' THEN d.dummy
END) As OldValue,
(CASE sc.name
WHEN 'code' THEN CAST(i.code AS VARCHAR)
WHEN 'name' THEN i.name
WHEN 'profession' THEN i.profession
WHEN 'dummy' THEN i.dummy
END) As NewValue
FROM inserted i
JOIN deleted d
ON i.code = d.code
JOIN syscolumns sc
ON sc.id = object_id('TestTrigger')
JOIN ColumnsOfInterest c
ON sc.name = c.name
WHERE ((CASE sc.name
WHEN 'code' THEN CAST(i.code AS VARCHAR)
WHEN 'name' THEN i.name
WHEN 'profession' THEN i.profession
WHEN 'dummy' THEN i.dummy
END) <>
(CASE sc.name
WHEN 'code' THEN CAST(d.code AS VARCHAR)
WHEN 'name' THEN d.name
WHEN 'profession' THEN d.profession
WHEN 'dummy' THEN d.dummy
END))
END
GO
UPDATE TestTrigger set name='rp'
GO
SELECT *
FROM AuditChanges
Hope This Helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply