A Chicken-and-Egg Trigger Problem

Hey, Ted:

Say I have two physical files: A and B. File A has an insert trigger program (written in RPG IV) that adds new records to B. That is, when someone adds a row to A, the trigger adds a row to B. What would happen if a program read B and added new rows to A?

insert into A (TransID, AnotherValue, Amount)
  select (TransID, AnotherValue, (0 - Amount) from B
     where substr(TransID,1,1) = 'T'

The intention is to zero-out the balances of all transaction IDs that begin with the letter T. That is, the sum of all records with a common TransID value will be zero after the new rows are inserted into the table.

However, I am afraid the trigger will pick up the new rows that I am inserting and will write even more rows to B, which will then be picked up by the SELECT, which will write more rows to B, which will be picked up by the SELECT, ad infinitum.

I realize that I could test this (programs A and B and the trigger program already exist), and I do plan to do such a test; however, even if the test is successful, I will be not feel comfortable putting the program into production. I am afraid the behavior of the database engine could change in a future release; maybe the select will ignore the new rows in V5R2 but not in V7R1.

So what I am looking for is a rule that says "a select within an insert is tested only once" or "a select is refreshed as new rows are inserted."

--Nathan

I called on the experts at IBM Rochester to find an answer for you. According to IBM, this is a variation of the Halloween problem, so named because it was discovered on October 31. The Halloween problem was discovered by IBMers who were working on the System R project, IBM's first foray into the world of relational database management systems.

The SQL standard prevents this problem with SQL triggers by requiring that all rows (records) be copied to a temporary table before running any triggers. No additional rows will be selected after the trigger runs. In DB2/400, this type of trigger is known as a MODE DB2SQL trigger.

DB2/400 also permits MODE DB2ROW triggers. This type of trigger is run immediately as the row is selected, possibly resulting in the Halloween problem. Triggers added with the Add Physical File Trigger (ADDPFTRG) CL command are of this type.

In your situation, you may or may not get the loop you describe. It depends on how SQL and the query optimizer work. If, for example, all the selected rows can be retrieved with a single fetch, the program probably won't loop.

The bottom line is that you need to use a MODE DB2SQL trigger to get the behavior you desire. However, since the existing trigger is a MODE DB2ROW-type trigger, you should revise your update strategy by selecting the B rows into a temporary table, then using the temporary table to drive the insert. With releases before V5R1, create and load a temporary table in the QTEMP library, using any method of your choosing. As of V5R2, you can use the CREATE GLOBAL TEMPORARY TABLE command in SQL to create a temporary table in QTEMP.

declare global temporary table temp1 as     
    (select * from b                           
     where substr(TransID,1,1) = 'T')          
  with data                                  

insert into a
    (select TransID, AnotherField, 0 - Amount
     from temp1)                               

--Ted

This article has been corrected since it was first published. A line in the last section of code originally read: "insert into b." The line now reads: "insert into a." Guild Companies regrets the error. [Correction made 2/25/04.]