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 use the INSTEAD OF option, just explicitly delete the rows at the end. For example:

CREATE 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 2

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