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 - 26 Apr 2007 18:13 GMT
> Problem: after inser trigger encounter error sql0348
>
[quoted text clipped - 10 lines]
> 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

Signature
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
wugon.net@gmail.com - 27 Apr 2007 01:10 GMT
> wugon....@gmail.com wrote:
> > Problem: after inser trigger encounter error sql0348
[quoted text clipped - 22 lines]
> DB2 Solutions Development
> IBM Toronto Lab
Thanks your advice.