Monday, March 28, 2011

How can I fire a trigger BEFORE a delete in T-SQL 2005?

How can I fire a trigger BEFORE a delete in T-SQL 2005? The FOR actually fires AFTER an event and they seems no BEFORE argument in the TRIGGER function. The INSTEAD OF is not what I want. I need to fire before I delete a record. Any ideas?

From stackoverflow
  • You can't. But you can perform a rollback in an AFTER DELETE trigger.

  • You can't. What you can do is check the DELETE table and undo the delete if you need to do so.

    Here's the Sequence of events:

    • Send DELETE Command to SQL Server
    • Item is removed from the table
    • OnDelete Trigger Fires Perform your
    • Logic If Logic fails/passes/whatever, undo delete
  • You can use the INSTEAD OF option, just explicitly delete the rows at the end. For example:

    CREATED TRIGGER dbo.My_Table_Delete_Instead_Of_Trigger
    ON dbo.My_Table
    INSTEAD OF DELETE
    AS
    BEGIN
    
         -- Do some stuff here
    
         DELETE T
         FROM DELETED D
         INNER JOIN dbo.My_Table T ON T.PK_1 = D.PK_1 AND T.PK_2 = D.PK_2
    END
    

    This assumed a primary key made up of columns PK_1 and PK_2.

    Greg : Thank you very much Tom
  • Instead of triggers happen before the delete so why do you say you don't want to use that?

    What exactly are you trying to do, knowing more about your situation would help us give you a better answer.

0 comments:

Post a Comment