Delete Trigger Examples

When you delete a primary-key row, also 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 are removed from 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.

Preventing Primary Key Deletions:

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.

Recording Errors That Occur:

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"