Triggers that return inconsistent results

When you create triggers and write SQL statements that activate those triggers, you need to ensure that executing those statements always produces the same results.

Two common reasons that you can get inconsistent results are:

Positioned UPDATE or DELETE statements that use uncorrelated subqueries cause triggers to operate on a larger result table than you intended.
DB2® does not always process rows in the same order, so triggers that propagate rows of a table can generate different result tables at different times.

The following examples demonstrate these situations.

Example: Effect of an uncorrelated subquery on a triggered action: Suppose that tables T1 and T2 look like this:

Table T1     Table T2
   A1           B1
   ==           ==
    1            1
    2            2

The following trigger is defined on T1:

CREATE TRIGGER TR1
  AFTER UPDATE OF T1
  FOR EACH ROW
  MODE DB2SQL
    BEGIN ATOMIC
      DELETE FROM T2 WHERE B1 = 2;
    END

Now suppose that an application executes the following statements to perform a positioned update operation:

EXEC SQL BEGIN DECLARE SECTION;
 long hv1;
EXEC SQL END DECLARE SECTION;
⋮
EXEC SQL DECLARE C1 CURSOR FOR
  SELECT A1 FROM T1
  WHERE A1 IN (SELECT B1 FROM T2)
  FOR UPDATE OF A1;
⋮
EXEC SQL OPEN C1;
⋮
while(SQLCODE>=0 && SQLCODE!=100)
{
  EXEC SQL FETCH C1 INTO :hv1;
  UPDATE T1 SET A1=5 WHERE CURRENT OF C1;
}

When DB2 executes the FETCH statement that positions cursor C1 for the first time, DB2 evaluates the subselect, SELECT B1 FROM T2, to produce a result table that contains the two rows of column T2:

1
2

When DB2 executes the positioned UPDATE statement for the first time, trigger TR1 is activated. When the body of trigger TR1 executes, the row with value 2 is deleted from T2. However, because SELECT B1 FROM T2 is evaluated only once, when the FETCH statement is executed again, DB2 finds the second row of T1, even though the second row of T2 was deleted. The FETCH statement positions the cursor to the second row of T1, and the second row of T1 is updated. The update operation causes the trigger to be activated again, which causes DB2 to attempt to delete the second row of T2, even though that row was already deleted.

To avoid processing of the second row after it should have been deleted, use a correlated subquery in the cursor declaration:

DCL C1 CURSOR FOR
  SELECT A1 FROM T1 X
  WHERE EXISTS (SELECT B1 FROM T2 WHERE X.A1 = B1)
  FOR UPDATE OF A1;

In this case, the subquery, SELECT B1 FROM T2 WHERE X.A1 = B1, is evaluated for each FETCH statement. The first time that the FETCH statement executes, it positions the cursor to the first row of T1. The positioned UPDATE operation activates the trigger, which deletes the second row of T2. Therefore, when the FETCH statement executes again, no row is selected, so no update operation or triggered action occurs.

Example: Effect of row processing order on a triggered action: The following example shows how the order of processing rows can change the outcome of an after row trigger.

Suppose that tables T1, T2, and T3 look like this:

Table T1     Table T2     Table T3
   A1           B1           C1
   ==           ==           ==
    1         (empty)      (empty)
    2

The following trigger is defined on T1:

CREATE TRIGGER TR1
  AFTER UPDATE ON T1
  REFERENCING NEW AS N
  FOR EACH ROW
  MODE DB2SQL
    BEGIN ATOMIC
      INSERT INTO T2 VALUES(N.C1);
      INSERT INTO T3 (SELECT B1 FROM T2);
    END

Now suppose that a program executes the following UPDATE statement:

UPDATE T1 SET A1 = A1 + 1;

The contents of tables T2 and T3 after the UPDATE statement executes depend on the order in which DB2 updates the rows of T1.

If DB2 updates the first row of T1 first, after the UPDATE statement and the trigger execute for the first time, the values in the three tables are:

Table T1     Table T2     Table T3
   A1           B1           C1
   ==           ==           ==
    2            2            2
    2

After the second row of T1 is updated, the values in the three tables are:

Table T1     Table T2     Table T3
   A1           B1           C1
   ==           ==           ==
    2            2            2
    3            3            2
                              3

However, if DB2 updates the second row of T1 first, after the UPDATE statement and the trigger execute for the first time, the values in the three tables are:

Table T1     Table T2     Table T3
   A1           B1           C1
   ==           ==           ==
    1            3            3
    3

After the first row of T1 is updated, the values in the three tables are:

Table T1     Table T2     Table T3
   A1           B1           C1
   ==           ==           ==
    2            3            3
    3            2            3
                              2