PRB: Rolling Back Individual Rows from an INSERT Trigger

ID: Q67303

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SYMPTOMS

The following insert trigger has been defined on a table named SALES:


   CREATE TRIGGER Part_check ON SALES
   FOR INSERT AS
   IF NOT EXISTS (SELECT * FROM Inventory inv,inserted ins
                 WHERE inv.part_no = ins.part_no)
  BEGIN
        ROLLBACK TRANSACTION
        PRINT "Part number not found in Inventory table."
        PRINT "-- Transaction rolled back --"
   END

The trigger works correctly when the data is inserted one row at a time.

There is another table called RETURNS, which is used to insert the rows from that table into the SALES table with the following command:


   INSERT Sales SELECT * FROM Returns

This works correctly if there are no rows that have invalid part numbers. However, if there is at least one row that is valid, then all of the rows get inserted, including those that do not have a matching part number in the INVENTORY table. Instead of having all the rows inserted, it is desired that the invalid rows not be allowed.

CAUSE

This problem occurs because triggers get fired only once per transaction. For example, if a single row is inserted with the following command, this is treated as a single transaction:


   INSERT Sales VALUES (337, "Small widgets")

The values are placed in the logical table INSERTED, and the trigger checks the table's values to see if they meet the qualifications of the trigger.

Now, suppose values are inserted from another table with the following command:


   INSERT Sales SELECT * FROM Returns

This is still treated as a single transaction. The trigger does not get fired for each row coming in. Rather, it waits until all rows are in the INSERTED logical table, and then checks those values to see if they meet the trigger criteria. In the example above, as long as there is at least one row that has a part number listed in the INVENTORY table, the WHERE EXISTS clause is satisfied, and all rows from the INSERTED table are inserted into the SALES table.

WORKAROUND

The following trigger can be used to ensure that only the qualifying rows get inserted:


   CREATE TRIGGER Part_check ON Sales
   FOR INSERT AS
   DELETE Sales FROM Sales, Inserted ins
   WHERE sales.part_no = ins.part_no
   AND NOT EXISTS (SELECT * FROM Inventory inv
                   WHERE inv.part_no = ins.part_no)

This trigger will allow all the rows to be inserted into the SALES table. Then the rows where the part number is not found in the INVENTORY table will be deleted.

Additional query words: trigger rollback transaction

Keywords : kbprg SSrvProg
Version : 4.2
Platform : OS/2
Issue type :