Insert Trigger Example Using Multiple Rows

Use a insert trigger to update a column everytime a row is added.

This insert trigger example updates the total_sales column in the titles table every time a new salesdetail row is added.

The trigger fires 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)