Transact-SQL User's Guide
|Chapter 16 Triggers: Enforcing Referential Integrity|
|Using triggers to maintain referential integrity|
|Delete trigger examples|
When you delete a primary key row, delete corresponding foreign key rows in dependent tables. This preserves referential integrity by ensuring that detail rows are removed when their master row is deleted. If you do not delete the corresponding rows in the dependent tables, you may end up with a database with detail rows that cannot be retrieved or identified. To properly delete the dependent foreign key rows, use a trigger that performs a cascading delete.Cascading delete example
When a delete statement on titles is executed, one or more rows leave the titles table and are added to deleted. A trigger can check the dependent tables--titleauthor, salesdetail, and roysched--to see if they have any rows with a title_id that matches the title_ids removed from titles and is now stored in the deleted table. If the trigger finds any such rows, it removes them.
create trigger delcascadetrig on titles for delete as delete titleauthor from titleauthor, deleted where titleauthor.title_id = deleted.title_id /* Remove titleauthor rows that match deleted ** (titles) rows.*/
delete salesdetail from salesdetail, deleted where salesdetail.title_id = deleted.title_id /* Remove salesdetail rows that match deleted ** (titles) rows.*/
delete roysched from roysched, deleted where roysched.title_id = deleted.title_id /* Remove roysched rows that match deleted ** (titles) rows.*/Restricted delete examples
In practice, you may want to keep some of the detail rows, either for historical purposes (to check how many sales were made on discontinued titles while they were active) or because transactions on the detail rows are not yet complete. A well-written trigger should take these factors into consideration.
The deltitle trigger supplied with pubs2 prevents the deletion of a primary key if there are any detail rows for that key in the salesdetail table. This trigger preserves the ability to retrieve rows from salesdetail:
create trigger deltitle on titles for delete as if (select count(*) from deleted, salesdetail where salesdetail.title_id = deleted.title_id) > 0 begin rollback transaction print "You cannot delete a title with sales." end
In this trigger, the row or rows deleted from titles are tested by being joined with the salesdetail table. If a join is found, the transaction is canceled.
Similarly, the following restricted delete prevents deletes if the primary table, titles, has dependent children in titleauthor. Instead of counting the rows from deleted and titleauthor, it checks to see if title_id was deleted. This method is more efficient for performance reasons because it checks for the existence of a particular row rather than going through the entire table and counting all the rows.
The next example uses raiserror for error message 35003. raiserror sets a system flag to record that the error occurred. Before trying this example, add error message 35003 to the sysusermessages system table:
sp_addmessage 35003, "restrict_dtrig - delete failed: row exists in titleauthor for this title_id."
The trigger is:
create trigger restrict_dtrig on titles for delete as if exists (select * from titleauthor, deleted where titleauthor.title_id = deleted.title_id) begin rollback transaction raiserror 35003 return end
To test this trigger, try this delete statement:
delete titles where title_id = "PS2091"