Chapter 19: Triggers: Enforcing Referential Integrity

Update trigger example using multiple rows

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

Conditional insert trigger example using multiple rows

View this book as PDF