Post reply

trigger - old value new value

  • November 25, 2015 at 12:55 pm

    #322811

    Hi Team,

    Below trigger on update - I want to get the before update - old value and after update new value new value

    using "d.@Columns_Updated, i.@Columns_Updated" to get the values, but getting error

    "Msg 102, Level 15, State 1, Procedure T1OnUpdate, Line 22

    Incorrect syntax near '@Columns_Updated'."

    Need your help to get this done..

    ALTER TRIGGER T1OnUpdate

    ON T1

    AFTER UPDATE

    AS

    DECLARE @idTable INT

    SELECT @idTable = T.id FROM sysobjects P JOIN sysobjects T ON P.parent_obj = T.id WHERE P.id = @@procid

    DECLARE @Columns_Updated VARCHAR(50);

    SELECT @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name

    FROM syscolumns

    WHERE id = @idTable

    AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1) > 0

    IF UPDATE (id) OR UPDATE (name)

    begin

    INSERT INTO T2 (id, col_name,old_val, new_val,upd_by)

    SELECT i.id, @Columns_Updated, d.@Columns_Updated, i.@Columns_Updated,i.usr_name

    FROM INSERTED i INNER JOIN DELETED d ON i.id = d.id

    end

  • November 25, 2015 at 1:36 pm

    #1842405

    Please stop making new threads for the same problem.

    Focus on a single thread to solve the issue completely and let people follow the advice you've been given.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

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