Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2007

Tip: Looking for answers? Try searching our database.

db2 LUW v8 after inser trigger encounter error sql0348

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
wugon.net@gmail.com - 26 Apr 2007 14:19 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.