Delete Trigger Example Using Multiple Rows

This delete trigger example 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)