Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: help with trigger that calls a stored procedure, win32 v9.5

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •