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"

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF