Microsoft KB Archive/67303

= PRB: Rolling Back Individual Rows from an INSERT Trigger =

Article ID: 67303

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q67303



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 &quot;Part number not found in Inventory table.&quot; PRINT &quot;-- Transaction rolled back --&quot; END

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

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

INSERT Sales SELECT * FROM Part_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, &quot;Small widgets&quot;)

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: kbprogramming KB67303

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.