Cascade on Delete or Use Triggers

Cascade on Delete or use Triggers?

CASCADE DELETE in MSSQL Server can only cascade to a single table. If you have two tables with foreign key relationships to a dimension table, you can only cascade delete to one of them. (This is to prevent deletes cascading through multiple paths and creating conflicts, much as C++ allows multiple inheritance but C# only allows single inheritance)

When this is the case, you are forced to use triggers or specifically handle the case in your code.

For this reason I have seen many people opt for using triggers in all cases. Even when there is only one foreign table. This ensures consistency and so people know what to look for when maintaining the database.

If one could cascade a delete to more than one table I would say it would be the most preferable option. This limitation, however, muddies the waters and I'm currently more in favour of triggers owning all such behaviours. The overhead in using triggers for cascaded deletes and updates is only minor in terms of coding, but does allow for standard practices that are truely generic.

EDIT:

You might want to move the 'accepted answer' to someone else, I've worked out I was wrong abot the above.

You CAN have multiple fact tables have ON DELETE CASCADE Foreign Key Contraints to a signle Dimension table.

What you Can't do is have one Fact Table have have ON DELETE CASCADE Foreign Key Constraints to multiple Dimension Tables.

So for example...

- Dimension Table [Person] (id INT IDENTITY, )

- Dimension Table [Exam] (id INT IDENTITY, )

- Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)

If either the Person or the Exam are deleted, you'd want the associated Exam_Score record(s) to also be deleted.

This is not possible using ON DELETE CASCADE in MS SQL Server, thus the need for triggers.

(Apologies to Mehrdad who tried to explain this to me but I completely missed his point.)

SQL Server using triggers and geting rid of ON DELETE CASCADE

As bad as triggers are but sometimes they are the only way to implement complex business requirements. I would do something as follows in the following example PK_ID refers to Primary Key Column.

CREATE TRIGGER tr_Table_A_InsteadOfDelete
ON dbo.TableA
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

BEGIN TRANSACTION;

    -- Insert into History Table from TableB

    INSERT INTO TABLE_B_History
    SELECT B.*
    FROM TableB B INNER JOIN deleted d
    ON B.PK_ID = d.PK_ID

    -- Delete rows from TableB

      DELETE FROM TableB
        WHERE EXISTS (SELECT 1 
                      FROM deleted 
                      WHERE PK_ID = TableB.PK_ID) 

    -- Insert into History Table from TableA

    INSERT INTO TABLE_A_History
    SELECT A.*
    FROM TableA A INNER JOIN deleted d
    ON A.PK_ID = d.PK_ID

    -- Delete rows from TableA

      DELETE FROM TableA
        WHERE EXISTS (SELECT 1 
                      FROM deleted 
                      WHERE PK_ID = TableA.PK_ID) 

COMMIT TRANSACTION;

END

MS SQL. Cascade Delete and triggers

IF OBJECT_ID('dbo.[DeliveryLine]', 'U') IS NOT NULL
    DROP TABLE dbo.[DeliveryLine]
GO
IF OBJECT_ID('dbo.[DeliveryHeader]', 'U') IS NOT NULL
    DROP TABLE dbo.[DeliveryHeader]
GO
CREATE TABLE dbo.DeliveryHeader (
    Id INT /*IDENTITY(1,1)*/ PRIMARY KEY
)
GO
CREATE TABLE dbo.DeliveryLine (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DeliveryHeaderId INT NOT NULL
)

ALTER TABLE dbo.DeliveryLine WITH CHECK
    ADD CONSTRAINT FK_DeliveryLine_DeliveryHeader FOREIGN KEY(DeliveryHeaderId)
    REFERENCES dbo.DeliveryHeader(Id)
    ON DELETE CASCADE
GO

CREATE TRIGGER [dbo].[trg_DeliveryLine_DeleteLog]   
    ON [dbo].[DeliveryLine]
    AFTER DELETE
AS 
BEGIN

    SET NOCOUNT ON;
    PRINT 'all works!!!'

END
GO

INSERT INTO dbo.DeliveryHeader (Id) VALUES (0)
INSERT INTO dbo.DeliveryLine (DeliveryHeaderId) VALUES (0)
GO

DELETE FROM dbo.DeliveryHeader WHERE Id = 0 -- trigger fire only when data exists in both tables

INSERT INTO dbo.DeliveryHeader (Id) VALUES (1) -- trigger not fire
DELETE FROM dbo.DeliveryHeader WHERE Id = 1

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.

on delete cascade not firing trigger

'Triggers are not activated by foreign key actions.'

http://dev.mysql.com/doc/refman/5.5/en/stored-program-restrictions.html

Ahhh!

Can anyone help me with a workaround?

MYSQL trigger for Cascading Delete : Workaround needed

There is no workaround.
Work the logic with code!

SQL Create Trigger Before Delete vs. Foreign Key On Delete

A FOREIGN KEY will restrict values that can be stored in the project_id column of the works_on table. You will not be able to set a value that does not exist in the project table.

A TRIGGER does not restrict the range of values that can be stored.

---

Related Topics