Nested SQL statements

Restrictions for nested SQL statements

Be aware of the following Db2 restrictions on nested SQL statements:

• Restrictions for SELECT statements:

  When you execute a SELECT statement on a table, you cannot execute INSERT, UPDATE, MERGE, or DELETE statements on the same table at a lower level of nesting.

  For example, suppose that you execute this SQL statement at level 1 of nesting:

  SELECT UDF1(C1) FROM T1;

  You cannot execute this SQL statement at a lower level of nesting:

  INSERT INTO T1 VALUES(…);

• Restrictions for SELECT FROM FINAL TABLE statements that specify INSERT, UPDATE, or DELETE statements to change data:
  When you execute this type of statement, an error occurs if both of the following conditions exist:

  • The SELECT statement that modifies data (by specifying INSERT, UPDATE, or DELETE) activates an AFTER TRIGGER.
  • The AFTER TRIGGER results in additional nested SQL operations that modify the table that is the target of the original SELECT statement that modifies data.
• Restrictions for INSERT, UPDATE, MERGE, and DELETE statements:

  When you execute an INSERT, UPDATE, MERGE, or DELETE statement on a table, you cannot access that table from a user-defined function or stored procedure that is at a lower level of nesting.

  For example, suppose that you execute this SQL statement at level 1 of nesting:

  DELETE FROM T1 WHERE UDF3(T1.C1) = 3;

  You cannot execute this SELECT statement at a lower level of nesting:

  SELECT * FROM T1;

Statement nesting for AFTER triggers

If the AFTER trigger is not activated by an INSERT, UPDATE, or DELETE data change statement that is specified in a data-change-table-reference SELECT FROM FINAL TABLE, the preceding list of restrictions do not apply to SQL statements that are executed at a lower level of nesting as a result of an after trigger. For example, suppose an UPDATE statement at nesting level 1 activates an after update trigger, which calls a stored procedure. The stored procedure executes two SQL statements that reference the triggering table: one SELECT statement and one INSERT statement. In this situation, both the SELECT and the INSERT statements can be executed even though they are at nesting level 3.

Although trigger activations count in the levels of SQL statement nesting, the previous restrictions on SQL statements do not apply to SQL statements that are executed in the trigger body.

CREATE TRIGGER TR1
 AFTER INSERT ON T1
 FOR EACH STATEMENT MODE DB2SQL
 BEGIN ATOMIC
  UPDATE T1 SET C1=1;
 END

INSERT INTO T1 VALUES(…);

Although the UPDATE statement in the trigger body is at level 2 of nesting and modifies the same table that the triggering statement updates, Db2 can execute the INSERT statement successfully.