Printable View
we need to report on what changes were made to one particular column of a table. We need to report the old value, the new value and the date and userid (these are also fields in the table).
I assume we use an update trigger with a query to capture the old and new values in a temp table?
Does anyone have any sample syntax for such a situation?
Sorry this isn't a reply I'm joining the thread because I would find an exmple useful as well.
David Westmore
------------
Scott at 4/6/01 1:01:10 AM
we need to report on what changes were made to one particular column of a table. We need to report the old value, the new value and the date and userid (these are also fields in the table).
I assume we use an update trigger with a query to capture the old and new values in a temp table?
Does anyone have any sample syntax for such a situation?
Hi Scott,
An update trigger would help. Check the example below.
CREATE TRIGGER Changes ON Yourtable
FOR UPDATE,DELETE
AS
-- Check for deleted/Old Record Value
-- You may insert into a temp table
-- and insert into your audit table.
-- Create your audittable before
-- your trigger.
-- You may a record id to make the
-- more meaningful
--Check for deleted/modified records
INSERT INTO AuditTable
SELECT ParticularColumn, Date,UserId
FROM DELETED d INNER JOIN Table
WHERE d.PK = t.PK -- (Pk=PrimaryKey)
AND d.date = getdate()
-- Check for Inserted/New Records
INSERT INTO AuditTable
SELECT ParticularColumn, Date,UserId
FROM INSERTED i INNER JOIN TABLE t
WHERE i.pk = t.pk
AND i.date = getdate()
-- Untested code.
I hope this help
:)