db2 LUW v8 after inser trigger encounter error sql0348

[wugon.net]

Problem: after inser trigger encounter error sql0348

Env:db2 v8 + fp 13 + win xp

Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348

but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.

did anyone can help to advice why db2 create triggers sequence will
get difference result ?
sample case as follow:
================================================== ========
connect sample @

drop view db2.sum @
drop trigger DB2.TRG1 @
drop trigger DB2.TRG2 @
drop table DB2.Q1 @
drop table DB2.Q2 @
drop table DB2.TEST1 @
drop table DB2.T1 @
drop table DB2.T2 @
drop sequence db2.test_1_seq @

CREATE SEQUENCE DB2.TEST_1_SEQ AS INTEGER
MINVALUE 1 MAXVALUE 2147483647
START WITH 1 INCREMENT BY 1
CACHE 10000 CYCLE NO ORDER @

create table db2.t1 (id int, name varchar(5), dep varchar(5), code
int ) @
create table db2.t2 (id int, name varchar(5), dep varchar(5), code
int ) @
create table DB2.TEST1 ( ID INT, NAME CHAR(10)) @
create table DB2.Q1 ( ID INT, NAME CHAR(10)) @
create table DB2.Q2 ( ID INT, NAME CHAR(10)) @

create view db2.sum ( id, name, code )
as
select id, name, dep
from db2.t1
where dep='A00'
union all
select id, name, dep
from db2.t1
where dep='B00' @
create trigger DB2.TRG1
after insert on DB2.TEST1
referencing
new as nw_row for each row
mode db2sql
BEGIN ATOMIC
IF ( (select count(*) from syscat.indexes) 10 )
THEN
insert into DB2.Q1 (ID, NAME)
values (
(select count(*) from DB2.SUM),
-- nw_row.ID,
nw_row.NAME) ;
ELSE
insert into DB2.Q1 (ID, NAME)
values ( (500+nw_row.ID),
nw_row.NAME) ;
END IF;
END @

create trigger DB2.TRG2
after insert on DB2.TEST1
referencing
new as nw_row for each row
mode db2sql
BEGIN ATOMIC
insert into DB2.Q2 (ID, NAME)
values ( NEXTVAL FOR DB2.TEST_1_SEQ,
nw_row.NAME) ;
END @
================================================== ========
Insert data:
db2 connect to sample
db2 "insert into db2.test1 values(11111,'xxyyzz') "
* will get error sql0348

if re-create trigger DB2.TRG2 before DB2.TRG1 , the trigger will
successfully without error.

Thanks

[Serge Rielau]

wu*******@gmail.com wrote:

Problem: after inser trigger encounter error sql0348

Env:db2 v8 + fp 13 + win xp

Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348

but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.

did anyone can help to advice why db2 create triggers sequence will
get difference result ?

That's a bug! Can you call support and get an APAR opened?
Your repro is good. If support gives you a headache refer them to me. :-)

Chers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[wugon.net]

On Apr 27, 1:13 am, Serge Rielau <[e-mail]:

[e-mail] wrote:

Problem: after inser trigger encounter error sql0348

Env:db2 v8 + fp 13 + win xp

Description:
we build two after insert triggers DB2.TRG1, DB2.TRG2 on base table
DB2.TEST1,
insert data into DB2.TEST1 encounter error sql0348

but we re-create trigger DB2.TRG2 before DB2.TRG1 and re-insert data
into DB2.TEST1 successfully.

did anyone can help to advice why db2 create triggers sequence will
get difference result ?

That's a bug! Can you call support and get an APAR opened?
Your repro is good. If support gives you a headache refer them to me. :-)

Chers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks your advice.