Conditional INSERT Trigger

Creating and Maintaining Databases

Creating and Maintaining Databases

Conditional INSERT Trigger

A trigger rejects or accepts each data modification transaction as a whole. 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.

Examples
A. Use an AFTER INSERT trigger

The following example assumes the existence of a table called newsale in the pubs database. This 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)

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.

CREATE TRIGGER conditionalinsert
ON sales
AFTER 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

When unacceptable titles have been inserted, 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 sales table and the inserted table, and then the trigger fires.

To test the 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:

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.