ID Number: Q67303
1.10 1.11 4.20
OS/2
Summary:
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.
RESOLUTION
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 reference words: 1.10 1.11 4.20 trigger rollback transaction