Cascading Deletes/Updates with Triggers

Triggers cannot be used to perform cascading updates and deletes if foreign key-to-primary key relationships, or foreign key-to-unique relationships, have been established using SQL Server's DRI. The DRI constraints are tested first; the trigger only fires if the update or delete passes all constraint restrictions. Therefore, because any update or delete that would need to be cascaded would fail the constraint checking, DRI FOREIGN KEY constraints must not exist on those relationships that need to be cascaded.

By not declaring the FOREIGN KEY (or REFERENCES) constraints, the cascading updates and deletes can be implemented using triggers. The PRIMARY KEY and UNIQUE constraints should still be used, however.

A delete trigger on the primary table either deletes the rows in the dependent table(s) or sets all corresponding foreign keys to NULL (or their default value). The cascading delete is easily performed with nested triggers, each deleting all rows in dependent tables. Cascading triggers that set null or defaults may be more problematic due to multirow considerations with triggers if the updates must be cascaded to additional levels of dependent tables. However, if the foreign keys are not part of the dependent table's primary key, they can be updated from within the trigger.

For implementing cascaded updates, an update trigger on the primary table should perform the required data modifications on the secondary table(s). Again, as long as the foreign key being updated is not part of the dependent table's primary key, it can be updated from within the trigger.

The following is an example of a cascading delete trigger on the titles table that deletes all rows in the titleauthor table with matching foreign key values. Because title_id is part of the primary key of titleauthor, this trigger assumes there are no subsequent levels of tables with foreign keys referring to titleauthor. This will work correctly even for multirow deletes.

CREATE TRIGGER DelCascadeTrig
    ON titles
    FOR DELETE
AS
    DELETE titleauthor
        FROM titleauthor, deleted
        WHERE titleauthor.title_id = deleted.title_id

The following is an example of setting a null within a delete trigger on the titles table that updates all rows in the titleauthor table with matching foreign key values. Again, because title_id is part of the primary key of titleauthor, this trigger assumes there are no subsequent levels of tables with foreign keys referring to titleauthor. This also works for multirow deletes.

CREATE TRIGGER DelSetNullTrig
    ON titles
    FOR DELETE
AS
    UPDATE titleauthor
        SET titleauthor.title_id = NULL
            FROM titleauthor, deleted
            WHERE titleauthor.title_id = deleted.title_id

The following is an example of a cascading update trigger on the titles table that updates all rows in the titleauthor table with matching foreign key values. Again, because title_id is part of the primary key of titleauthor, this trigger assumes there are no subsequent levels of tables with foreign keys referring to titleauthor.

CREATE TRIGGER UpdCascadeTrigBad
    ON titles
    FOR UPDATE
AS
    IF UPDATE(title_id)
    BEGIN
        UPDATE titleauthor
            SET titleauthor.title_id = inserted.title_id
                FROM titleauthor, deleted, inserted
                WHERE titleauthor.title_id = deleted.title_id
        END
    END

This will NOT work correctly for multirow updates because there is no way to match a given row in the deleted table with its corresponding row in the inserted table without adding a second unique identifier that never changes its value. This is the same problem that arises when cascading needs to be taken to subsequent levels and the foreign key is part of the primary key in the dependent table and the primary key in the dependent table is referred to by other foreign keys.

To prevent multirow updates, the preceding trigger should be rewritten to prevent the update from affecting more than one row of the original table (titles, in this case). The update in the trigger may well update more than one row in titleauthor; however, this solution to the multirow problem might just cause the problem to reappear at the next level of the cascade.

CREATE TRIGGER UpdCascadeTrig
    ON titles
    FOR UPDATE
AS
    IF UPDATE(title_id)
    BEGIN
      IF @@ROWCOUNT = 1
        UPDATE titleauthor
            SET titleauthor.title_id = inserted.title_id
                FROM titleauthor, deleted, inserted
                WHERE titleauthor.title_id = deleted.title_id
      ELSE
        ROLLBACK TRANSACTION
        RAISERROR ('Multirow update on table "titles" not allowed.')
      END
    END

For multiple levels of cascading triggers to function properly, the "nested triggers" sp_configure parameter must be 1. Triggers can only be nested to 16 levels.

sp_configure nested 'triggers',1