Chapter 19: Triggers: Enforcing Referential Integrity

Delete trigger example using multiple rows

The next example is a delete trigger that updates the total_sales column in the titles table every time one or more salesdetail rows are deleted.

create trigger deltrig 
on salesdetail 
for delete 
as 
    /* check value of @@rowcount */ 
if @@rowcount = 1 
    update titles 
      set total_sales = total_sales - qty 
      from deleted 
      where titles.title_id = deleted.title_id 
else 
    /* when rowcount is greater than 1, 
       use a group by clause */ 
    update titles 
      set total_sales = 
        total_sales - (select sum(qty)
      from deleted 
      group by deleted.title_id 
      having titles.title_id = deleted.title_id)

This trigger goes into effect whenever a row is deleted from the salesdetail table. It updates the total_sales column in the titles table so that total_sales is equal to its previous value minus the value subtracted from salesdetail.qty.

Update trigger example using multiple rows

View this book as PDF