Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Red face Unanswered: trigger body with a procedure call..

    hi,

    i ve some doubts regarding the trigger body, kindly clarify me ..

    i am not able to use SQLCODE, CURSORS, Procedure calls.. in a trigger body ..

    is there any constraints for using this in a trigger...

    also one query regarding the cursor..

    is it possible to use a select query with more than one table in a cursor definition..

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SQL Statement support can be found here :

    http://publib.boulder.ibm.com/infoce...d/r0007085.htm
    If you are on atleast 8.2, you can call a SP from a trigger

    HTH

    Sathyaram

    Quote Originally Posted by Shefu
    hi,

    i ve some doubts regarding the trigger body, kindly clarify me ..

    i am not able to use SQLCODE, CURSORS, Procedure calls.. in a trigger body ..

    is there any constraints for using this in a trigger...

    also one query regarding the cursor..

    is it possible to use a select query with more than one table in a cursor definition..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the reply sathya,

    i have gone thru the table u ve suggested.. it says 'CALL' statement can be used in a trigger.
    hence i wrote a test trigger with a procedure call. It compiled successfully, but executing the trigger had no effect on the procedure call. ie., the execution of the procedure doesn t happened(it should display the result set in the command window on execution). does it means that procedure calls are not supported inside a trigger.

    also i tried a cursor within a trigger. i am not able to compile it. Any lights to throw on this..


    regards
    Sn

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You haven't mentioned you db2 version, fixpack, operating system etc.

    post your trigger and sp code

    Sathyaram


    Quote Originally Posted by Shefu
    Thanks for the reply sathya,

    i have gone thru the table u ve suggested.. it says 'CALL' statement can be used in a trigger.
    hence i wrote a test trigger with a procedure call. It compiled successfully, but executing the trigger had no effect on the procedure call. ie., the execution of the procedure doesn t happened(it should display the result set in the command window on execution). does it means that procedure calls are not supported inside a trigger.

    also i tried a cursor within a trigger. i am not able to compile it. Any lights to throw on this..


    regards
    Sn
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2005
    Posts
    127
    Hi,

    The DB2 Version is 8.2.0
    OS : Windows 2000

    Below is the test procedure sp_print called in the trigger,

    Code:
    CREATE PROCEDURE sp_print
    
    DYNAMIC RESULT SETS 1
    LANGUAGE     SQL
    
    
    BEGIN
    
    
    	DECLARE   s_temp_cursor    CURSOR    WITH HOLD     WITH RETURN TO CLIENT 	FOR   SELECT *	FROM  result_table FOR READ ONLY;
    
    	open s_temp_cursor;
    
    END
    @
    The trigger trig_test that calls the above procedure,

    Code:
    CREATE TRIGGER trig_test  AFTER INSERT  ON  test
    REFERENCING NEW_TABLE AS INSERTED
    FOR EACH STATEMENT
    MODE DB2SQL
    TRIGR:  BEGIN ATOMIC
    
    	INSERT INTO  result_table VALUES ( 1 ) 	;
    
    	call sp_print;
    
    END
    @
    regards,
    Sn

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Cursors in triggers are not supported.

    Were you able to execute an insert statement successfully after you defined the SP and trigger ? I get -746
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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