Post reply

Comparing Data in a Trigger

  • March 17, 2004 at 1:28 pm

    #178717

    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

  • March 17, 2004 at 2:09 pm

    #499208

    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

  • March 17, 2004 at 2:22 pm

    #499215

    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.

  • March 18, 2004 at 6:59 am

    #499311

    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 Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 19, 2004 at 9:02 am

    #499519

    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

  • March 19, 2004 at 10:16 am

    #499537

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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