Post reply

TRIGGER: UPDATE, DELETE or INSERT?

  • October 5, 2010 at 2:10 am

    #228946

    Hi,

    I hope a simple question. When I create a trigger on update, insert and delete, is there a way to find out which of these events fired the trigger?

    Here is some pseudocode:

    CREATE TRIGGER justatest

    ON testtable

    FOR UPDATE, INSERT, DELETE

    AS

    BEGIN

    IF @@update-fired-trigger = 1

    INSERT INTO log 'a record was updated'

    ELSE IF @@insert-fired-trigger = 1

    INSERT INTO log 'a record was inserted'

    ELSE IF @@delete-fired-trigger = 1

    INSERT INTO log 'a record was deleted'

    END

    Thanks

  • October 5, 2010 at 2:16 am

    #1231508

    IF EXISTS (SELECT 1 FROM inserted)

    IF EXISTS (SELECT 1 FROM deleted)

    PRINT 'Is Update'

    ELSE

    PRINT 'Is Insert'

    ELSE

    PRINT 'Is Delete'

    Basically, if there are rows in both inserted and deleted, it's an update. If there are rows only in inserted, it's an insert. If there are rows only in deleted, it's a delete.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 5, 2010 at 2:24 am

    #1231513

    Thanks for your reply.... it's clear!

    Ray

  • October 6, 2010 at 1:46 am

    #1232285

    t

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

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