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

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

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

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

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

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.