After Delete Trigger Fires Only After Delete?

Question

I thought "after delete" meant that the trigger is not fired until after the delete has already taken place, but here is my situation...

I made 3, nearly identical SQL CLR after delete triggers in C#, which worked beautifully for about a month. Suddenly, one of the three stopped working while an automated delete tool was run on it.

By stopped working, I mean, records could not be deleted from the table via client software. Disabling the trigger caused deletes to be allowed, but re-enabling it interfered with the ability to delete.

So my question is 'how can this be the case?' Is it possible the tool used on it futzed up the memory? It seems like even if the trigger threw an exception, if it is AFTER delete, shouldn't the records be gone?

All the trigger looks like is this:

ALTER TRIGGER [sysdba].[AccountTrigger] ON [sysdba].[ACCOUNT]  AFTER  DELETE AS 
EXTERNAL NAME [SQL_IO].[SQL_IO.WriteFunctions].[AccountTrigger]
GO

The CLR trigger does one select and one insert into another database. I don't yet know if there are any errors from SQL Server Mgmt Studio, but will update the question after I find out.

UPDATE:

Well after re-executing the same trigger code above, everything works again, so I may never know what if any error SSMS would give.

Also, there is no call to rollback anywhere in the trigger's code.

Answer 1

An error in the AFTER DELETE trigger will roll-back the transaction. It is after they are deleted but before the change is committed. Is there any particular reason you are using a CLR trigger for this? It seems like something that a pure SQL trigger ought to be able to do in a possibly more lightweight manner.

Answer 2

after means it just fires after the event, it can still be rolled back

example

create table test(id int)
go


create trigger trDelete on test after delete
as

print 'i fired '
rollback

do an insert

insert test values (1)

now delete the data

delete test

Here is the output from the trigger

i fired

Msg 3609, Level 16, State 1, Line 1

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

now check the table, and verify that nothing was deleted

select * from test

The CLR trigger does one select and one insert into another database. I don't yet know if there are any errors from SQL Server Mgmt Studio, but will update the question after I find out.

Suddenly, one of the three stopped working while an automated delete tool was run on it.

triggers fire per batch/statement not per row, is it possible that your trigger wasn't coded for multi-row operations and the automated tool deleted more than 1 row in the batch? Take a look at Best Practice: Coding SQL Server triggers for multi-row operations

Here is an example that will make the trigger fail without doing an explicit rollback

alter trigger trDelete on test after delete
as

print 'i fired '
declare @id int
select @id = (select id from deleted)
GO

insert some rows

insert test values (1)
insert test values (2)
insert test values (3)

run this

delete test

i fired Msg 512, Level 16, State 1, Procedure trDelete, Line 6

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

check the table

select * from test

nothing was deleted

Answer 3

Well you shouldn't be doing a select in trigger (who will see the results) and if all you are doing is an insert it shouldn't be a CLR trigger either. CLR is not generally a good thing to have in a trigger, far better to use t-SQL code in a trigger unless you need to do something that t-sql can't handle which is probably a bad idea in a trigger anyway.

Have you reverted to the last version you have in source control? Perhaps that would clear the problem if it has gotten corrupted.