Can anyone tell me why this create trigger stmt fails? It tells me the trigger is defined with an unsupported triggered sql statement, but I can't figure out why. The docs say a BEFORE trigger can call a stored proc if it doesn't modify sql data. What am I missing?
Thanks!


-- create a test table
CREATE TABLE TMP_TEST (col1 varchar(20))

-- create a simple test stored procedure
DROP PROCEDURE TMP_TEST_SP
CREATE PROCEDURE TMP_TEST_SP ( IN var1 varchar(32) )
P1: BEGIN
SET var1 = 'bar';
END P1

-- test procedure
CALL TMP_TEST_SP ('foo')

-- create a trigger that calls the SP, why doesn't this work?
CREATE TRIGGER TMP_TEST_TRIGGER
BEFORE INSERT ON TMP_TEST
REFERENCING NEW AS inserted
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
CALL TMP_TEST_SP('foo');
END

-- drop everything
DROP TRIGGER TMP_TEST_TRIGGER
DROP PROCEDURE TMP_TEST_SP
DROP TABLE TMP_TEST



Here is the error:
The trigger "DB2ADMIN.TMP_TEST_TRIGGER" is defined with an unsupported triggered SQL statement.. SQLCODE=-797, SQLSTATE=42987, DRIVER=3.53.71


C:\DB2\REPLWORK>db2 ? sql-797


SQL0797N The trigger "<trigger-name>" is defined with an unsupported
triggered SQL statement.

Explanation:

The trigger must be defined with a triggered SQL statement that can only
include statements from the following list.
* A trigger can include the following control statements:
* dynamic compound statement
* FOR statement
* GET DIAGNOSTICS statement
* IF statement
* ITERATE statement
* LEAVE statement
* SIGNAL statement
* WHILE statement

* A BEFORE trigger can also include the following triggered SQL
statements:
* a CALL statement which invokes a procedure that does not modify
SQL data
* a fullselect
* an assignment statement



However, the triggered SQL statements in a BEFORE trigger cannot
include:
* a table function that modifies SQL data
* a nested DELETE, INSERT, MERGE, or UPDATE statement

* An AFTER trigger can also include the following triggered SQL
statements:
* an INSERT statement
* a searched UPDATE statement
* a searched DELETE statement
* a MERGE statement
* a CALL statement
* a fullselect
* an assignment statement (excluding assignment of transition
variables)

* An INSTEAD OF trigger can also include the following triggered SQL
statements:
* an INSERT statement
* a searched UPDATE statement
* a searched DELETE statement
* a MERGE statement
* a CALL statement
* a fullselect
* an assignment statement (excluding assignment of transition
variables)

In some cases the "<trigger-name>" is not available for use in the
message.

User response:

Check the triggered SQL statements in the trigger for any statement that
does not match the above list and remove it.

sqlcode: -797

sqlstate: 42987

If you look at the CREATE PROCEDURE syntax diagram you will notice that the statement defaults to MODIFIES SQL DATA, if that clause is not specified explicitly as something else (e.g. CONTAINS SQL). Procedure declaration is the only way for the compiler to determine whether it modifies data or not.