Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: can we call DB2 stored procedure from DB2 triggers

    Hi, I am working in db2 version 7 under windows 2000. Basically i am porting oracle triggers to DB2 triggers. With some limitations in DB2 triggers (like cursors ), I wanna write those logic in stored procs and call those stored procs from my triggers. Can I do this?

    Having real hard time porting oracle triggers to DB2 triggers!! Can anyone help me out please!

    Thks in advance!
    Sha...

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Surely we can call stored procedures from a trigger.
    A single trigger can fire off a UDF and/or stored procedure to invoke an external action.
    There is currently a safe limit to the cascading of triggers, stored procedures, and UDFs - runtime nesting depth of 16.
    Go through the SQL reference for the exact syntax for the same.
    -Prashant G Dahalkar
    Prashant

  3. #3
    Join Date
    Apr 2003
    Posts
    6

    Unhappy

    hmmmm....i am also having problems calling a sp from a trigger....

    CREATE TRIGGER tri_herman1
    AFTER INSERT ON HERMAN1
    REFERENCING NEW AS n
    FOR EACH ROW MODE DB2SQL
    CALL trans_pcd('table','s','a',current timestamp,null,123,'1');
    @

    This is the error message i receive:
    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 trans_pcd" was found following "MODE
    DB2SQL ". Expected tokens may include: "<values>". LINE NUMBER=5.
    SQLSTATE=42601

    How do you call a sp from a trigger in db2 v8.1?

  4. #4
    Join Date
    Jan 2003
    Posts
    24
    If I remember correctly, calling SP's from triggers is currently only supported in DB2 for OS/390, not in DB2 for Linux/Unix/Windows.

    /Daniel

  5. #5
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    I just got out of an IBM class last week. Calling SP's from a trigger is NOT supported.
    Linux, AIX, Windows DB2 UDB V8

Posting Permissions

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