Transact-SQL User's Guide

Multirow Considerations

Multirow considerations are particularly important when the function of a trigger is to recalculate summary values, or provide ongoing tallies.

Triggers used to maintain summary values should contain group by clauses or subqueries that perform implicit grouping. This creates summary values when more than one row is being inserted, updated, or deleted. Since a group by clause imposes extra overhead, the following examples are written to test whether @@rowcount = 1, meaning that only one row in the trigger table was affected. If @@rowcount = 1, the trigger actions take effect without a group by clause.

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) 

Delete Trigger Example Using Multiple Rows

The next example is a delete trigger that updates the total_sales column in the titles table every time one or more salesdetail rows are deleted.

create trigger
deltrig 
on salesdetail 
for delete 
as 
    /* check value
of @@rowcount */ 
if @@rowcount = 1 
    update titles 

set total_sales = total_sales - qty 
      from deleted 

where titles.title_id = deleted.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 deleted 
      group by
deleted.title_id 
      having titles.title_id = deleted.title_id)

This trigger goes into effect whenever a row is deleted from the salesdetail table. It updates the total_sales column in the titles table so that total_sales is equal to its previous value minus the value subtracted from salesdetail.qty.

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

The triggers examined so far have looked at each data modification statement as a whole. If one row of a four-row insert was unacceptable, the whole insert was unacceptable and the transaction was rolled back.

However, you do not have to roll back all data modifications simply because some of them are unacceptable. Using a correlated subquery in a trigger can force the trigger to examine the modified rows one by one. See "Using Correlated Subqueries" for more information on correlated subqueries. The trigger can then take different actions on different rows.

The following trigger example assumes the existence of a table called junesales. Here is its create statement:

create table junesales

(stor_id    char(4)     not null, 
ord_num     varchar(20) not
null, 
title_id    tid         not null, 
qty         smallint
not null, 
discount    float       not null) 

You should insert four rows in the junesales table, in order to test the conditional trigger. Two of the junesales rows have title_ids that do not match any of those already in the titles table.

insert junesales values ("7066",
"BA27619", "PS1372", 75, 40)

insert junesales values ("7066",
"BA27619", "BU7832", 100, 40)

insert junesales values ("7067",
"NB-1.242", "PSxxxx", 50, 40)

insert junesales values ("7131",
"PSyyyy", "PSyyyy", 50, 40)

When you insert data from junesales into salesdetail, the statement looks like this:

insert salesdetail 
select * from junesales 

What if you want to examine each of the records you are trying to insert? The trigger conditionalinsert analyzes the insert row by row and deletes the rows that do not have a title_id in titles:

create trigger conditionalinsert 
on salesdetail 
for
insert as 
if 
(select count(*) from titles, inserted 
where
titles.title_id = inserted.title_id) 
    != @@rowcount 
begin

  delete salesdetail from salesdetail, inserted 
    where
salesdetail.title_id = inserted.title_id
    and inserted.title_id
not in 
    (select title_id from titles) 
  print "Only records
with matching title_ids 
    added."
end 

The trigger deletes the unwanted rows. This ability to delete rows that have just been inserted relies on the order in which processing occurs when triggers are fired. First, the rows are inserted into the table and the inserted table; then, the trigger fires.