trigger body with a procedure call..

Shefu · #1 (**permalink**) 01-11-06, 11:26

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..

sathyaram_s · #2 (**permalink**) 01-11-06, 12:37

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..

Shefu · #3 (**permalink**) 01-11-06, 23:41

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

sathyaram_s · #4 (**permalink**) 01-12-06, 03:35

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

Shefu · #5 (**permalink**) 01-12-06, 05:51

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

sathyaram_s · #6 (**permalink**) 01-12-06, 08:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech