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

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.