Conditional Insert Trigger

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. This is the way triggers work. A trigger rejects or accepts each data modification transaction as a whole.

However, you don't 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 following example assumes the existence of a table called newsale. Here is the CREATE statement for newsale:

CREATE TABLE newsale
(stor_id char(4),
ord_num varchar(20),
date datetime,
qty smallint,
payterms varchar(12),
title_id tid)

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 then deletes the rows that do not have a title_id in titles. Here's how:

CREATE TRIGGER conditionalinsert
ON sales
FOR INSERT AS
IF
(SELECT COUNT(*) FROM titles, inserted
WHERE titles.title_id = inserted.title_id) <> @@rowcount
BEGIN
    DELETE sales FROM sales, inserted
    WHERE sales.title_id = inserted.title_id AND
        inserted.title_id NOT IN
            (SELECT title_id
            FROM titles)
    PRINT 'Only sales records with matching title_ids added.'
END

The trigger test is the same as the one in the insert trigger (intrig) example shown earlier, but the transaction is not rolled back. Instead, the trigger deletes the unwanted rows. This ability to delete rows that have been inserted relies on the order in which processing occurs when triggers are fired. First rows are inserted into the table and the inserted table, and then the trigger fires.

To test the conditional trigger, insert four rows in the newsale table. Two of the newsale rows have title_ids that do not match any of those already in the titles table. Here is the data to insert:

newsale

stor_id	ord_num	date	qty	payterms	title_id
-------	--------	-------------------	---	--------	--------
7066	QA7442.3	Jul 25 1995 8:35AM	75	Net 30	PS1372
7066	QA7442.3	Jul 24 1995 8:35AM	75	Net 60	BU7832
7067	D4482	Jul 27 1995 12:00AM	10	Net 30	PSxxxx
7131	N914008	Jul 27 1995 12:00AM	20	Net 30	PSyyyy

Next insert data from newsale into sales. The statement looks like this:

INSERT sales
SELECT * FROM newsale

The title_ids PSxxxx and PSyyyy do not match any in the titles table, and the conditionalinsert trigger deletes these two rows from the sales and inserted tables.