After Delete Trigger Fires Only After Delete?
- by Brandi
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.
