Insert trigger example using multiple rows

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) 

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF