Hello there,

I am experiencing a problem whereby ON EXCEPTION statements are
not firing within stored procedures when the stored procedures
are invoked from a trigger.

Is this a bug, or is this behaviour documented somewhere?

Either way, it's rather annoying - can it be changed? Are there any
workarounds? I could test for whether the exception would occur first,
but that means extra work to guard against race conditions.

I can reproduce this under IDS versions 9.40.UC7 and 10.00.FC5
(both on Linux).

Here's a (fairly) minimal test case that shows the problem:

=== BEGIN SQL ===

-- Create a table called tFoo with a primary key column called "a".
-- We explicitly name the index (not that this makes any difference).

create table tFoo (a int not null) lock mode row;
create unique index iFoo_x1 on tFoo(a);
alter table tFoo add constraint (
primary key (a)
constraint cFoo_pk
);

-- Create a stored procedure, pFoo, which inserts a row with a = 99
-- into tFoo. It uses the "on exception" technique to avoid raising
-- an error if such a row already exists.

create procedure pFoo ()

begin

on exception in (-268, -239)
-- Ignore a uniqueness or PK constraint violation.
end exception with resume;

insert into tFoo (a) values (99);

end

end procedure;

-- Call pFoo a few times to check it works:

execute procedure pFoo();
execute procedure pFoo();
execute procedure pFoo();

select * from tFoo;

-- Now create a table called tBar with an insert trigger which
-- calls the pFoo stored procedure.

create table tBar (b int not null, primary key (b)) lock mode row;
create trigger riBar insert on tBar
for each row
(
execute procedure pFoo()
);

-- Cause the trigger on tBar to fire:

insert into tBar (b) values (1);

=== END SQL ===

On running the above from dbaccess, I get:

268: Unique constraint (kelby.cfoo_pk) violated.
100: ISAM error: duplicate value for a record with unique key.

from the "insert into tBar ..." line.

And yet pFoo works fine when called directly.

Any feedback appreciated,
Regards,
Kieran Elby

On 15 Oct, 18:51, kie...@dunelm.org.uk wrote:
>
> I am experiencing a problem whereby ON EXCEPTION statements are
> not firing within stored procedures when the stored procedures
> are invoked from a trigger.
>
> Is this a bug, or is this behaviour documented somewhere?
>

Hi, to answer my own question, it has been pointed out to me (off-
list) that this is a documented feature.

The last paragraph of the ON EXCEPTION page in the SQL Syntax guide
(http://publib.boulder.ibm.com/infoce...ex.jsp?topic=/
com.ibm.sqls.doc/sqls946.htm) points out that:

"ON EXCEPTION has no effect within a UDR that is called by a trigger."

Furthermore, the "Constraint Checking" part of the "Cascading
Triggers" section
of the "CREATE TABLE" page of the same manual (http://
publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/
com.ibm.sqls.doc/sqls362.htm) says:

"When you use logging, the database server defers constraint
checking on the triggering statement until after the statements
in the triggered-action list execute.

"This is equivalent to executing a SET CONSTRAINTS ALL DEFERRED
statement before executing the triggering statement."

The example there goes on to demonstrate why this feature can be
useful.

Guess I'll have to read the fabulous manual better in future.

Still, it's not exactly obvious behaviour!

Regards,
Kieran Elby