The following update trigger updates the total_sales column in the titles table every time the qty field in a salesdetail row is updated. Recall that an update is an insert followed by a delete. This trigger references both the inserted and the deleted trigger test tables.
create trigger updtrig
on salesdetail
for update
as
if update (qty)
begin
/* check value of @@rowcount */
if @@rowcount = 1
update titles
set total_sales = total_sales +
inserted.qty - deleted.qty
from inserted, deleted
where titles.title_id = inserted.title_id
and inserted.title_id = deleted.title_id
else
/* when rowcount is greater than 1,
use a group by clause */
begin
update titles
set total_sales = total_sales +
(select sum(qty)
from inserted
group by inserted.title_id
having titles.title_id =
inserted.title_id)
update titles
set total_sales = total_sales -
(select sum(qty)
from deleted
group by deleted.title_id
having titles.title_id =
deleted.title_id)
end
end
