Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Unanswered: Problem calling Stored Procedure from Db2 Triggers

    Hi All,

    I am experiencing a small problem when calling the stored procedure
    from my triggers. I am using DB2 V8.1 Express Edition

    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
    ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
    FOR EACH ROW MODE DB2SQL
    WHEN ( od.totoId <> nw.totoId )
    BEGIN ATOMIC
    CALL myproc(3);
    END@

    I have the following error after issuing the command:

    [server@sup SCRIPTS]$ db2 -td@ -vf schema_test_trigger.db2
    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW FOR EACH ROW MODE DB2SQL WHEN ( od.totoId <> nw.totoId ) BEGIN ATOMIC CALL myproc(3); END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "CALL myproc" was found following "otoId ) BEGIN
    ATOMIC". Expected tokens may include: "<compound_return>". LINE NUMBER=1.
    SQLSTATE=42601


    I would appreciate if someone can help me.

    Thanks
    Last edited by Dave Rush; 06-30-04 at 07:00.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As of now, you cannot call a SP from within a trigger ...

    DB2 Stinger(GA later this year) provides this feature

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    15
    Thanks for your response,
    Is it possible to execute an sql statement like this with EXEC SQL or EXEC IMMEDIAT
    Then can I do this instead:

    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId
    ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW
    FOR EACH ROW MODE DB2SQL
    WHEN ( od.totoId <> nw.totoId )
    BEGIN ATOMIC
    DECLARE query VARCHAR(256);
    SET query = 'update toto set (totoId)=(3) where totoId=1'
    EXEC SQL :query;
    END@

    I got the following error still:
    [db2inst1@hs204 SCRIPTS]$ db2 -td@ -vf schema_test_trigger.db2
    CREATE TRIGGER DB2INST1.TEST AFTER UPDATE OF totoId ON DB2INST1.TOTO REFERENCING OLD AS od NEW AS nw OLD_TABLE AS OLD NEW_TABLE AS NEW FOR EACH ROW MODE DB2SQL WHEN ( od.totoId <> nw.totoId ) BEGIN ATOMIC DECLARE query VARCHAR(256); SET query = 'update toto set (totoId)=(3) where totoId=1'; EXEC SQL :query; END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "SQL" was found following "here totoId=1';
    EXEC". Expected tokens may include: "JOIN <joined_table>". LINE NUMBER=1.
    SQLSTATE=42601

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Look at the CREATE TRIGGER definition in the SQL reference - it lists all statements you can use in the trigger body.
    The following list of SQL-control-statements can be used within the
    dynamic compound statement:
    - FOR Statement
    - GET DIAGNOSTICS Statement
    - IF Statement
    - ITERATE Statement
    - LEAVE Statement
    - SIGNAL Statement
    - WHILE Statement
    The SQL statements that can be issued are:
    - fullselect64
    - Searched UPDATE
    - Searched DELETE
    - INSERT
    - SET variable 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
  •