"on exception" statement not firing within stored procedure when called from trigger

"on exception" statement not firing within stored procedure when called from trigger

Post by kiera » Wed, 17 Oct 2007 02:51:49

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 ()


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

insert into tFoo (a) values (99);


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,
Kieran Elby

"on exception" statement not firing within stored procedure when called from trigger

Post by kiera » Wed, 17 Oct 2007 17:29:22

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://www.yqcomputer.com/
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://
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

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

Still, it's not exactly obvious behaviour!

Kieran Elby