The following insert trigger updates the total_sales column in the titles table every time a new salesdetail row is added. It goes into effect whenever you record a sale by adding a row to the salesdetail table. It updates the total_sales column in the titles table so that total_sales is equal to its previous value plus the value added to salesdetail.qty. This keeps the totals up to date for inserts into salesdetail.qty.
create trigger intrig
on salesdetail
for insert as
/* check value of @@rowcount */
if @@rowcount = 1
update titles
set total_sales = total_sales + qty
from inserted
where titles.title_id = inserted.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 inserted
group by inserted.title_id
having titles.title_id = inserted.title_id)
