Comparing Data in a Trigger

  • Hello Everyone,

    Is there anyway that I can compare the same field data in a trigger. I need a way to keep track of before and after of the updated data. I hope this makes sense

    CREATE TRIGGER [Log] ON [dbo].[WPPI_Main_Load_Globals]

    FOR UPDATE

    AS

    DECLARE @msg varchar(4500), @newfield varchar(2000), @oldfield varchar(2000)

    IF UPDATE(prefix)

    BEGIN

    SELECT @newfield=i.prefix, @oldfield=c.prefix

    FROM inserted i

    INNER JOIN dbo.WPPI_Main_Load_Globals c ON i.ODBC = c.ODBC

    SET @msg = SYSTEM_USER + ' ' + CAST(GETDATE() as varchar(20)) + ' New Prefix: ' + @newfield + ' Old Prefix: ' + @oldfield

    END

    Thanks,

    Ken

  • SELECT @newfield=i.prefix, @oldfield=c.prefix

    FROM inserted i

    INNER JOIN dbo.WPPI_Main_Load_Globals c ON i.ODBC = c.ODBC

    change it to

    SELECT @newfield=i.prefix, @oldfield=d.prefix

    FROM inserted i

    INNER JOIN deleted d ON i.ODBC = d.ODBC

    -- I am assuming ODBC is pk

    BTW that only works if only one row is affected at a time


    * Noel

  • Thanks, noeld that works perfectly. I assume how it works is, that because it is an update that both the inserted and deleted "objects" are available.

  • What are doing with @msg after it is set?  Because as noel said what you are doing a=only works for single row updates.  IF you are logging the action in a table you could do:

    insert into log_table

    (

    action

    )

    Select

    SYSTEM_USER + ' ' + CAST(GETDATE() as varchar(20)) + ' New Prefix: ' + I.prefix+ ' Old Prefix: ' + D.prefix

    From

     

  • Jack,

    For the most part this table is read only. The data used in the table is used to load a DTS package Globals at runtime for execution. In the past few weeks I've notice that some of the data in this table get change, inturn causing the DTS package to fail. I need to know who is changing it!

    Ken

  • I thought something like that might be te case so having an audit table that you are inserting into can really give you a history of what is going on and allow you to identify patterns.  I would even break each peice into a separate column. changed_by, date_changed, old_val, new_val.  Then you can query the table to determine what is going on and by whom.  I don't know how the data is being altered, but if it is being done using SQL you could have multiple rows affected and you would only be logging one with the solution you currently have in place.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply