Trigger calling INVALID SP - strange behaviour ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Reddin - 29 Nov 2004 18:06 GMT

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

Reply to this Message

Serge Rielau - 29 Nov 2004 19:09 GMT

> Hi,
>
[quoted text clipped - 20 lines]
> 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

Reply to this Message

Paul Reddin - 30 Nov 2004 09:54 GMT

> 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

Reply to this Message

andreyp#Antispam@mapsitnA#it4profit.com - 30 Nov 2004 16:01 GMT

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*/

Reply to this Message