Post reply

Get current record value before nad after update trigger?

  • April 1, 2005 at 10:19 am

    #66173

    Hi!

    With a trigger statement after insert, how to get 'old" and "new" value?

    Please show me!

    Thx!

  • April 1, 2005 at 11:26 am

    #550162

    You should be able to reference the inserted table for the new value and the deleted table for the 'old' values.

     

     

  • April 1, 2005 at 1:02 pm

    #550187

    I think he is looking for the keywords.....

    It is buried in the BOL CREATE TRIGGER as

    deleted and inserted are logical (conceptual)
tables. They are structurally similar to the table
on which the trigger is defined, that is, the
table on which the user action is attempted, and
hold the old values or new values of the rows that
may be changed by the user action. For example, to
retrieve all values in the deleted table, use: 

SELECT *
FROM deleted

    And here is an example trigger:

    CREATE TRIGGER LoginTracking 
    ON TCL.dbo.SECUSER
FOR UPDATE
    AS IF UPDATE (USERCOUNT)
BEGIN
    INSERT INTO Narc_UserLogins 
    (WkStID, Process_ID, USERDESC, USERNAME,
     CHANGETIME, Curr_Count, New_Count)
    SELECT HOST_NAME() as WkStID, @@SPID as Process_ID,
    SECUSER.USERDESC, SECUSER.USERNAME,
    GETDATE() AS CHANGETIME, deleted.USERCOUNT,
    SECUSER.USERCOUNT
    FROM SECUSER, DELETED
    WHERE SECUSER.USERNAME = DELETED.USERNAME
END



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

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

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