Calling stored procedure in a trigger

hi,
i am working on a migration project from Oracle to db2 8.1. i was able to
migrate the stored procedures and functions in oracle to db2 with the help
of the migration tool kit.
now finally when i came to the trigger i am having problems. the trigger is
not getting compiled. Triggers call the stored procedures and functions in
them.

For example.
--the values passed in the parameter are all varchar only

CREATE TRIGGER t_content NO CASCADE BEFORE DELETE ON TCAT_CONTENT_COLLATERAL
REFERENCING OLD AS OLD FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
CALL sp_delete ('Category', old.category_id, old.collateral_id );
CALL sp_update ('DCS_CATEGORY', 'category_id', old.category_id);
END;

The error received was.

DB21034E The command was processed as an SQL statement because it was not
avalid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "CALL sp_delete" was found
following "DB2SQL BEGIN ATOMIC". Expected tokens may
include:"<compound_return>". LINE NUMBER=1. SQLSTATE=42601

Thanx in advance
mail to pjr@srasys.co.in

regards
Premnath

--
Message posted via http://www.dbmonster.com

re: Calling stored procedure in a trigger

Prem via DBMonster.com wrote:
[color=blue]
> hi,
> i am working on a migration project from Oracle to db2 8.1. i was able
> to
> migrate the stored procedures and functions in oracle to db2 with the help
> of the migration tool kit.
> now finally when i came to the trigger i am having problems. the trigger
> is
> not getting compiled. Triggers call the stored procedures and functions in
> them.
>
> For example.
> --the values passed in the parameter are all varchar only
>
> CREATE TRIGGER t_content NO CASCADE BEFORE DELETE ON
> TCAT_CONTENT_COLLATERAL REFERENCING OLD AS OLD FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> CALL sp_delete ('Category', old.category_id, old.collateral_id );
> CALL sp_update ('DCS_CATEGORY', 'category_id', old.category_id);
> END;[/color]

You might want to consider upgrading to V8.2 because CALL is supported in
triggers with that release.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

re: Calling stored procedure in a trigger

Prem via DBMonster.com wrote:[color=blue]
> hi,
> i am working on a migration project from Oracle to db2 8.1. i was able to
> migrate the stored procedures and functions in oracle to db2 with the help
> of the migration tool kit.
> now finally when i came to the trigger i am having problems. the trigger is
> not getting compiled. Triggers call the stored procedures and functions in
> them.
>
> For example.
> --the values passed in the parameter are all varchar only
>
> CREATE TRIGGER t_content NO CASCADE BEFORE DELETE ON TCAT_CONTENT_COLLATERAL
> REFERENCING OLD AS OLD FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> CALL sp_delete ('Category', old.category_id, old.collateral_id );
> CALL sp_update ('DCS_CATEGORY', 'category_id', old.category_id);
> END;
>
> The error received was.
>
> DB21034E The command was processed as an SQL statement because it was not
> avalid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "CALL sp_delete" was found
> following "DB2SQL BEGIN ATOMIC". Expected tokens may
> include:"<compound_return>". LINE NUMBER=1. SQLSTATE=42601[/color]
Couple of problems:
1. CALL in Trigger is a DB2 V8.2 feature
2. Apparently you try to MODIFY SQL DATA in these SQL Procedures. BEFORE
TRIGGERS are not meant to mondifye SQL DATA
(i.e do UPDATE, DELETE, INSERT, MERGE)
You will need to turn this trigger into an AFTER trigger (shouldn't be a
big deal)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab