Micro Optimizer Micro Optimizer - 27 days ago 14
SQL Question

Where is the flaw in my trigger to override DELETE with a soft-delete?

I have a trigger created with

CREATE TRIGGER [CantDeleteStuff] ON [dbo].[Stuff]
INSTEAD OF DELETE
AS
BEGIN
ROLLBACK
UPDATE [dbo].[Stuff] SET [Deleted]=1 FROM DELETED WHERE [dbo].[Stuff].[Id] = DELETED.[Id]
END
GO


and I think it's intent is clear. But when I try to delete a row I get the error


The transaction ended in the trigger. The batch has been aborted.


How to fix?

Answer

Instead of Delete will replace the Delete with your triggering code.

According to Technet, the Rollback in your trigger is the issue. You can read more here.

A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.

When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.

When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction. When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:

All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.

The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.

A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed. However, STATIC or INSENSITIVE cursors are left open if:

CURSOR_CLOSE_ON_COMMIT is set OFF. The static cursor is either synchronous or a fully populated asynchronous cursor. Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.

https://technet.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

So just remove the Rollback.

Comments