Results 1 to 5 of 5

Thread: Triggers

  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Question Unanswered: Triggers

    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

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    58
    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

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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.
    Last edited by urquel; 10-21-04 at 13:03.

  5. #5
    Join Date
    Oct 2003
    Posts
    58
    urquel,

    Many thanks.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •