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.
