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”. 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 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.
