Hi all,

I have written a set of triggers to capture any changes on gaiven table. At the moment when a change is made the triggers captures all of the fields associated with the changed record, and puts it in an identical table in another database. Is there a way I could write the trigger to capture only the changed fields from a given record? a sort of dynamic capture????

The exapmle below obnly has to deal with a few fields but this can get confusing with larger tables.

Here is the example code:

CREATE TRIGGER [dbo].[update_Cent]
ON dbo.Cent
AFTER UPDATE
AS

INSERT INTO [Audit].[dbo].[Cent_AUD]
(StatusID, Status, [Description],
ChangeType, ChangeDate, UserName)

SELECT StatusID, Status, [Description],
'Update', getdate(), user
FROM deleted

Thanks,

Andy

What DBMS are you using? Does it support a WHEN clause? If so, you can code the WHEN clause to examine the transition variables to see if the NEW. <> OLD. and use the values of the transition variables in your INSERT statement. Also, if you are only interested in an audit trail on specific columns of a table, you might consider using the UPDATE OF to limit the trigger to the columns in question. Of course, this all depends upon your design needs.

urquel,

Thank you.

The databse/s are on a SQL 2000 server, triggers run through enterprise manager.

I am relativley new to the DB game so the triggers are relatively crude, i would imagine it would support the WHERE clause i will give that a go. At the moment it serves a purpose, but as the user number and the DB size grows the call for recalling specific audit data is growing. Unfortunately the changes could be on most fields on any table, that is why it would be great if the trigger could only capture the changed data.

How could I implement it on the example given?

Regards,

Andy

Not a WHERE clause(predicate logic). A WHEN clause(trigger logic). (I am using DB2-based syntax. You will have to interpret for SQL2000)

CREATE TRIGGER [dbo].[update_Cent]
AFTER UPDATE
ON dbo.Cent
REFERENCING OLD AS OLD
NEW AS NEW
WHEN (OLD.StatusID <> NEW.StatusID
or OLD.Status <> NEW.Status
or OLD.[Description]<> NEW.[Description]
or ...)

BEGIN
INSERT INTO [Audit].[dbo].[Cent_AUD]
(StatusID, Status, [Description],
ChangeType, ChangeDate, UserName)
Values
(NEW.StatusID, NEW.Status, NEW.[Description], NEW.ChangeType, NEW.Change Date, NEW.UserName);
END

Or, based upon your trigger's capabilites, separate the evaluations of the columns and code the insert statements based upon what changed.

urquel,

Many thanks.

Andy