Delete trigger not being executed

Question

I have a table Absence with an PK AbscenceId (yes spelling mistake). I have another table Note, with an FK into Absence called AbsenceId (spelled correct this time). The FK constraint is:

ALTER TABLE [dbo].[Note]  WITH CHECK ADD  CONSTRAINT [FK_Note_Absence] FOREIGN KEY([AbsenceId])
REFERENCES [dbo].[Absence] ([AbscenceId])
GO

When an Absence is deleted, I want all Notes to go with it. I couldn't do this with CASCADES, because a Note can belong to something else as well.

So I created a trigger to delete the notes when an Absence is deleted:

ALTER TRIGGER [dbo].[TR_OnAbsenceDelete] ON [dbo].[Absence]
    FOR DELETE AS

    DELETE FROM [Note]
        WHERE AbsenceId IN ( SELECT AbscenceId FROM Deleted )

But when I delete an Absence that has a Note attached to it, I get:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Note_Absence". The conflict occurred in database "ReturnToWork", table "dbo.Note", column 'AbsenceId'.
The statement has been terminated.�

It is almost like the trigger is not getting executed?

Answer 1

Your trigger needs to be written as an INSTEAD OF trigger, deleting first from Note and then from Absence.

CREATE TRIGGER [dbo].[TR_OnAbsenceDelete] ON [dbo].[Absence]
    INSTEAD OF DELETE AS
BEGIN

    DELETE FROM n
        FROM deleted d
            INNER JOIN Note n
                ON d.AbscenceId = n.AbsenceId

    DELETE FROM a
        FROM deleted d
            INNER JOIN Absence a
                ON d.AbscenceId = a.AbscenceId
END