Results 1 to 3 of 3

Thread: triggers to monitor data changes

  1. #1
    Scott Guest

    triggers to monitor data changes


    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?

  2. #2
    David Westmore Guest

    triggers to monitor data changes (reply)

    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?

  3. #3
    Join Date
    Jul 2003
    Posts
    142
    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
    Last edited by olutimi; 08-26-2003 at 03:41 AM.

Posting Permissions

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