Why doesn't this cascading nullify trigger work on SQL Server 2000
I've got the tables A and B. Table B has a foreign key constraint refering to table B. But since SQL Server 2000 only supports cascading delete, and not nullify, I decided to fix this with a trigger.

The problem: the trigger below doesn't work:


    CREATE TRIGGER dbo.TR_A_B_CascadingNullify ON dbo.A
    FOR DELETE
    AS
    	UPDATE dbo.B
    	SET idA = NULL
    	WHERE idA IN (SELECT id FROM DELETED)
    ;

When deleting a row in A that isn't refered to, everything works. But when a refered to row is deleted, it fails on the foreign key constraint with the message `DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'. The conflict occurred in database 'x', table 'B', column 'idA'.`

And finally, when setting the option `Enforce Foreign Key Constraint` to `No`, the trigger does work, and the column in table B is set to NULL.

What am I doing wrong?
Why doesn't this cascading nullify trigger work on SQL Server 2000
===

I've got the tables A and B. Table B has a foreign key constraint refering to table B. But since SQL Server 2000 only supports cascading delete, and not nullify, I decided to fix this with a trigger.

The problem: the trigger below doesn't work:


    CREATE TRIGGER dbo.TR_A_B_CascadingNullify ON dbo.A
    FOR DELETE
    AS
    	UPDATE dbo.B
    	SET idA = NULL
    	WHERE idA IN (SELECT id FROM DELETED)
    ;

When deleting a row in A that isn't refered to, everything works. But when a refered to row is deleted, it fails on the foreign key constraint with the message `DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'. The conflict occurred in database 'x', table 'B', column 'idA'.`

And finally, when setting the option `Enforce Foreign Key Constraint` to `No`, the trigger does work, and the column in table B is set to NULL.

What am I doing wrong?