SQL Server: Self-Reference Fk, Trigger Instead of on Delete Cascade

SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE

The FOR DELETE trigger is raised after the original DELETE has been executed. To delete recursively, you need to write an INSTEAD OF DELETE trigger.

The algorithm is like this:

• Insert the PKs from deleted into a temp table

• Find detail records of records in temp table

• Loop until no more records are found

DELETE records in the table by joining with temp table.

I described recursive deletion in my blog.

Update

I guess you just need to drop that ON DELETE CASCADE flag from your recursive foreign key in Categories. The CASCADE flag on the foreign key from CAT_SCH should not matter.

On delete cascade for self-referencing table

Assuming you're keeping your FOREIGN KEY constraint in place, you cannot fix the issue in a FOR DELETE trigger. FOR triggers (also known as AFTER triggers) fire after the activity has taken place. And a foreign key will prevent a row from being deleted if it has references. Foreign key checks occur before deletion.

What you need is an INSTEAD OF trigger. You also need to bear in mind that your current trigger only tried to deal with one "level" of referencing. (So, if row 3 references row 2 and row 2 references row 1, and you delete row 1, your trigger only tried to remove row 2)

So, something like:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    DELETE FROM Comments
    WHERE id in (select id from IDs);

If there are other (non-self-referencing) cascading foreign key constraints, they all have to be replaced by actions in this trigger. In such a case, I'd recommend introducing a table variable to hold the list of all IDs that will eventually be deleted from the Comments table:

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    declare @deletions table (ID varchar(7) not null);
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    insert into @deletions(ID)
    select ID from IDs

    -- Delete from FK referenced table
    DELETE FROM OtherTable
    WHERE CommentID in (select ID from @deletions)

    --This delete comes last
    DELETE FROM Comments
    WHERE id in (select ID from @deletions);

INSTEAD OF DELETE Trigger conflict with ON DELETE CASCADE FK

I know this is an old question, but it deserves an answer:

The reason why you cannot specify ON DELETE CASCADE when your child table has an INSTEAD OF DELETE trigger defined is because in your trigger you may decide to not delete the child table rows thus impeding the cascade to take effect.

Since there is no certainty that a cascade is possible the database doesn't know how to handle that situation and thus leaves the issue to the developer to solve.

SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE

The FOR DELETE trigger is raised after the original DELETE has been executed. To delete recursively, you need to write an INSTEAD OF DELETE trigger.

The algorithm is like this:

• Insert the PKs from deleted into a temp table

• Find detail records of records in temp table

• Loop until no more records are found

DELETE records in the table by joining with temp table.

I described recursive deletion in my blog.

Update

I guess you just need to drop that ON DELETE CASCADE flag from your recursive foreign key in Categories. The CASCADE flag on the foreign key from CAT_SCH should not matter.

Self referencing foreign-key constraints and delete

Unlike Andomar, I'd be happy using a trigger - but I wouldn't remove the constraint checking. If you implement it as an instead of trigger, you can reset the other rows to null before performing the actual delete:

CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)

It's short, it's succinct, it wouldn't be necessary if SQL Server could handle foreign key cascades to the same table (in other RDBMS', you may be able to just specify ON DELETE SET NULL for the foreign key constraint, YMMV).

On delete cascade error - How can I solve it with trigger?

Their is many ways to solve your problem. I will present only two solutions:

1- The simpliest one: Changing your table Poll_Vote_Table into 2 table one for Questions, one for Answers.

2- Use trigger instead of delete/update. It's actually what you want, but please consider solution 1. Now the code to use trigger (for reference):

(I'll just illustrate for the Question part. For Answer, it's the same.)

First you will have to recreate your FK as follow (after delete) :

ALTER TABLE [dbo].[Poll_Vote_Table]  WITH CHECK 
ADD CONSTRAINT [FK_Vote_Question] FOREIGN KEY([PollQuestionId])
REFERENCES [dbo].[Poll_Question_Table] ([PollQuestionId])

Then you will need to create the triggers :

CREATE TRIGGER [DELETE_Question_Vote]
   ON dbo.[Poll_Question_Table]
   INSTEAD OF DELETE
AS 
BEGIN
 SET NOCOUNT ON;
 DELETE FROM [Poll_Vote_Table] WHERE PollQuestionId IN (SELECT PollQuestionId FROM DELETED)
 DELETE FROM [Poll_Question_Table] WHERE PollQuestionId IN (SELECT PollQuestionId FROM DELETED)
END
GO

The update part is usually of no use, so I won't write about it but it's basicly the same as DELETE.

---

Related Topics