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

问题:

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?

回答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.

回答2:

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

回答3:

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