Conditional Insert Trigger Example Using Multiple Rows

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.

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) 

Insert four rows in the junesales table, 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 

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 
(select count(*) from titles, inserted 
where titles.title_id = inserted.title_id) 
    != @@rowcount 
  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 

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.

Related concepts
Correlated Subqueries