Post reply

Which Trigger type fired?

  • February 7, 2005 at 6:57 pm

    #66881

    If a trigger is created for Insert, Update and Delete is there a way within the trigger to determine which action caused the trigger to fire?

    I would like to know if I can reference the Inserted table or the Deleted table.

    I'm using sql 2000.

    Thanks - ron

  • February 7, 2005 at 11:36 pm

    #541064

    Yes, there is a way, and it's related to the inserted and deleted tables.

    For an insert, only the inserted table will have rows.

    For an update, both inserted and deleted will have rows (inserted containing the new data, deleted the old)

    For a delete, only deleted will have rows.

    You access inserted and deleted as you would any other table. The two tables will have exactly the same columns as the table been inserted/updated/deleted

    Hence, you can do stuff like

    IF EXISTS(SELECT * FROM inserted)

    ...

    Or you can use it in joins with other tables eg.

    INSERT INTO Audit (OldDescription, NewDescription, ChangedBy, ChangeDate)

    SELECT deleted.Descr, inserted.Descr, Person.UserName,GetDATE()

    FROM deleted inner join inserted on deleted.id=inserted.id inner join Person on Inserted.ChangedBy=Person.ID

    (rough example that assumes that the ids weren't changed)

    Check Books Online, the page titled Creating a Trigger for more details.

    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

  • February 8, 2005 at 7:57 am

    #541121

    That was very helpful.  Thanks.

    ron

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

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