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

Trigger calling INVALID SP - strange behaviour ?

P: n/a
Hi,

FYI & comment:

We have triggers that call SPs.

If the Stored Procedure is invalidated, e.g a dependent table is dropped.
The SP is marked as invalid, but no error is thrown by the trigger!!

i.e the trigger performs no action, but because there is no error, this can
easily go unnoticed (as it has been in our scenario)

Is this working as designed? surely you would want the trigger to error?

We have coded around this by checking a return code from the SP for NULL (which
it seems to be when the SP is invalid)

i.e
declare rc_sqlstate char(5) default '00000'

call my_stored_proc(...... OUT rc_sqlstate);

if ((rc_sqlstate <> '00000') or (rc_sqlstate is null))THEN
ERROR ....
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Paul Reddin wrote:
Hi,

FYI & comment:

We have triggers that call SPs.

If the Stored Procedure is invalidated, e.g a dependent table is dropped.
The SP is marked as invalid, but no error is thrown by the trigger!!

i.e the trigger performs no action, but because there is no error, this can
easily go unnoticed (as it has been in our scenario)

Is this working as designed? surely you would want the trigger to error?

We have coded around this by checking a return code from the SP for NULL (which
it seems to be when the SP is invalid)

i.e
declare rc_sqlstate char(5) default '00000'

call my_stored_proc(...... OUT rc_sqlstate);

if ((rc_sqlstate <> '00000') or (rc_sqlstate is null))THEN
ERROR ....

Which platform are you on?
What should happin is that the stored procedure tries to rebind upon
CALL. If you haven't recreated the table by that time you should get an
error stating that an implicit rebind failed.
This shoudl be returned by teh CALL statement which shoudl fail the
trigger and roll back teh entire statement.
So... I would expect a:
SQLCODE -723 (trigger failed) with embeddend token: <sqlcode/state of
implicit rebind>, -204 (object not found), <tablename>
Something like that.

Cheres
Serge
Nov 12 '05 #2

P: n/a
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<31*************@uni-berlin.de>...
Which platform are you on?
V8.2 Linux (RHAT).
What should happin is that the stored procedure tries to rebind upon
CALL. If you haven't recreated the table by that time you should get an
error stating that an implicit rebind failed.
Doesn't look like it's doing this.

We'll investigate further and raise a PMR. I was keen to know
if anybody else was/has experienced this?


Cheres
Serge

Nov 12 '05 #3

P: n/a
after drop depended table. But on REBIND invalid_package db2 say SUCCESS.
May be it's no good...

And may be You known method how dba can find thats packages (after success
rebind)?

Andy

create procedure test
language sql
begin
select 1 from test;
end

drop table test /* package for sp test in invalid state */

rebind package for sp test /* look on successfully*/
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.