By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,436 Members | 2,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,436 IT Pros & Developers. It's quick & easy.

Calling stored procedure in a trigger

P: n/a
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 pj*@srasys.co.in

regards
Premnath

--
Message posted via http://www.dbmonster.com
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Prem via DBMonster.com wrote:
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;


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
Nov 12 '05 #2

P: n/a
Prem via DBMonster.com wrote:
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

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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.