"on exception" statement not firing within stored procedure when called from trigger
- From: kieran@xxxxxxxxxxxxx
- Date: Mon, 15 Oct 2007 10:51:49 -0700
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
.
- Follow-Ups:
- Prev by Date: Re: Minimum ESQL/C version for DESCRIBE INPUT?
- Next by Date: Re: Minimum ESQL/C version for DESCRIBE INPUT?
- Previous by thread: Minimum ESQL/C version for DESCRIBE INPUT?
- Next by thread: Re: "on exception" statement not firing within stored procedure when called from trigger
- Index(es):
Relevant Pages
|
