Insert prob

yogesh kansal

Insert prob
Hi all
I am using db2 7.2 on Linux .
I have a table named abc
create table abc ( person_id varchar(10) not null primary key ,
name varchar(100));


. and i have written trigger on before insert and after insert as ..

create trigger abctrig no cascade before insert on abc referencing new
as n for each row mode DB2SQL set n.PERSON_ID=( select
'PP'||right('0000000'||rtrim(char(id)),8) from IDGEN)

create trigger abctrig2 after insert on abc referencing new as n for
each row mode DB2SQL update IDGEN set ID = ID +1

i have a id gen table as
create table idgen ( id bigint ) ;

these triggers r written to generate a unique id ..


now the prob is that when i do a insert on table abc as

insert into abc (person_id , name ) as select '', name from persons ;

the prob is this insert is giving me error as primary key voilation ..
The prob is that when i insert through select , DB is not calling the
triggers . The triggers r working fine if i give a simple insert .
Can any body help me ??

thanks
yogesh

--


--
Yogesh Kansal
Davlin Software Pvt Ltd .
2nd Floor , Punja Building,
Lalbaugh , Mangalore
Ph 0824-451001 ext - 19

"For every problem there is always a solution that is simple, obvious, and wrong !! "



Peter Backlund

Re: Insert prob
(in response to yogesh kansal)
Yogesh,

when you have an "insert select", your construction
works perfectly when the select returns one row.
If there are several rows in the select, all the before
triggers are applied first, and the idgen table returns the same value
for all rows

Peter

yogesh kansal wrote:

> Hi all
> I am using db2 7.2 on Linux .
> I have a table named abc
> create table abc ( person_id varchar(10) not null primary key ,
> name varchar(100));
>
>
> . and i have written trigger on before insert and after insert as ..
>
> create trigger abctrig no cascade before insert on abc referencing new
> as n for each row mode DB2SQL set n.PERSON_ID=( select
> 'PP'||right('0000000'||rtrim(char(id)),8) from IDGEN)
>
> create trigger abctrig2 after insert on abc referencing new as n for
> each row mode DB2SQL update IDGEN set ID = ID +1
>
> i have a id gen table as
> create table idgen ( id bigint ) ;
>
> these triggers r written to generate a unique id ..
>
>
> now the prob is that when i do a insert on table abc as
>
> insert into abc (person_id , name ) as select '', name from persons ;
>
> the prob is this insert is giving me error as primary key voilation ..
> The prob is that when i insert through select , DB is not calling the
> triggers . The triggers r working fine if i give a simple insert .
> Can any body help me ??
>
> thanks
> yogesh
>
> --
>
>
> --
> Yogesh Kansal
> Davlin Software Pvt Ltd .
> 2nd Floor , Punja Building,
> Lalbaugh , Mangalore
> Ph 0824-451001 ext - 19
>
> "For every problem there is always a solution that is simple, obvious,
> and wrong !! "
>
>
>
> visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
> the list can
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



yogesh kansal

Re: Insert prob
(in response to Peter Backlund)
Thanks peter ,

Oh no !!i means either i have to write some stored procedure to do that
or few of the tricky things like .. export and then import ..

Or can u suggest me some good way abt how to do that ?

yogesh


Peter Backlund wrote:

> Yogesh,
>
> when you have an "insert select", your construction
> works perfectly when the select returns one row.
> If there are several rows in the select, all the before
> triggers are applied first, and the idgen table returns the same value
> for all rows
>
> Peter
>
> yogesh kansal wrote:
>
>> Hi all
>> I am using db2 7.2 on Linux .
>> I have a table named abc
>> create table abc ( person_id varchar(10) not null primary key ,
>> name varchar(100));
>>
>>
>> . and i have written trigger on before insert and after insert as ..
>>
>> create trigger abctrig no cascade before insert on abc referencing new
>> as n for each row mode DB2SQL set n.PERSON_ID=( select
>> 'PP'||right('0000000'||rtrim(char(id)),8) from IDGEN)
>>
>> create trigger abctrig2 after insert on abc referencing new as n for
>> each row mode DB2SQL update IDGEN set ID = ID +1
>>
>> i have a id gen table as
>> create table idgen ( id bigint ) ;
>>
>> these triggers r written to generate a unique id ..
>>
>>
>> now the prob is that when i do a insert on table abc as
>>
>> insert into abc (person_id , name ) as select '', name from persons ;
>>
>> the prob is this insert is giving me error as primary key voilation ..
>> The prob is that when i insert through select , DB is not calling the
>> triggers . The triggers r working fine if i give a simple insert .
>> Can any body help me ??
>>
>> thanks
>> yogesh
>>
>> --
>>
>>
>> --
>> Yogesh Kansal
>> Davlin Software Pvt Ltd .
>> 2nd Floor , Punja Building,
>> Lalbaugh , Mangalore
>> Ph 0824-451001 ext - 19
>>
>> "For every problem there is always a solution that is simple, obvious,
>> and wrong !! "
>>
>>
>>
>> visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
>> the list can
>>
>
> --
>
> =====> See you in October, 2003 at IDUG in Nice, France <======
>
> +-------------------------------+---------------------------------+
> | Peter G Backlund | Telephone: 08 - 38 69 08 |
> | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
> | Smastuguvagen 2 | Country Code (Sweden): 46 |
> | S-165 72 HASSELBY | |
> | Sweden | E-mail: [login to unmask email] |
> +-------------------------------+---------------------------------+
> | IBM Database GOLD Consultant |
> +-------------------------------+---------------------------------+
> | Working with DB2 since 1981 ... and forever! |
> +-----------------------------------------------------------------+
>
>
>
> visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
> the list can
>

--
Yogesh Kansal
Davlin Software Pvt Ltd .
2nd Floor , Punja Building,
Lalbaugh , Mangalore
Ph 0824-451001 ext - 19

"For every problem there is always a solution that is simple, obvious, and wrong !! "



David Seibert

Re: Insert prob
(in response to yogesh kansal)
Hello Yogesh,

Can you use the Sequence object to create your Unique ids?
Sequences solve most of the problems with the Identity feature.

With them you don't need a Stored procedure or trigger or complex program
logic to create unique numeric values.

See
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d
2w/en_main

and select the SQL reference or Admin guide for information about sequences.


David Seibert
Compuware Corporation Database Product Architect
[login to unmask email]






The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.



yogesh kansal

Re: Insert prob
(in response to David Seibert)
hi david
Thanks for reply .
The prob is that coding is done and i can't change the id gen tables to
sequences .....
i have done this ...



drop trigger trigname ;

insert into tablename (PERSON_ID,FIRST_NAME,LAST_NAME)
select 'PP'||right('0000000'||rtrim(char(rownumber() over() +1+ (
select id from PER_PRO_IDGEN))),8)
,First_Name,Last_name from MES_TABLE ;

and then again create trigger .....

and it's working now ..

but i don't know why db2 was not calling the before trigger after each
insert . the interesting thing is that db2 has called the after insert
triggers.

yogesh



Seibert, Dave wrote:

>Hello Yogesh,
>
> Can you use the Sequence object to create your Unique ids?
>Sequences solve most of the problems with the Identity feature.
>
>With them you don't need a Stored procedure or trigger or complex program
>logic to create unique numeric values.
>
>See
>http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d
>2w/en_main
>
>and select the SQL reference or Admin guide for information about sequences.
>
>
>David Seibert
>Compuware Corporation Database Product Architect
>[login to unmask email]
>
>
>
>
>
>
>The contents of this e-mail are intended for the named addressee only. It
>contains information that may be confidential. Unless you are the named
>addressee or an authorized designee, you may not copy or use it, or disclose
>it to anyone else. If you received it in error please notify us immediately
>and then destroy it.
>
>
>
>
>
>

--
Yogesh Kansal
Davlin Software Pvt Ltd .
2nd Floor , Punja Building,
Lalbaugh , Mangalore
Ph 0824-451001 ext - 19

"For every problem there is always a solution that is simple, obvious, and wrong !! "



Isaac Yassin

Re: Insert prob
(in response to yogesh kansal)
Hi,

I guess I have some understanding problem
<snip>
why db2 was not calling the before trigger after each insert
</snip>
As far as I know "before trigger" is called before the action, not after
it :-)

Or is it mis-typing ?

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of yogesh kansal
Sent: Monday, January 13, 2003 3:41 PM
To: [login to unmask email]
Subject: Re: Insert prob


hi david
Thanks for reply .
The prob is that coding is done and i can't change the id gen tables to
sequences ..... i have done this ...



drop trigger trigname ;

insert into tablename (PERSON_ID,FIRST_NAME,LAST_NAME)
select 'PP'||right('0000000'||rtrim(char(rownumber() over() +1+ (
select id from PER_PRO_IDGEN))),8)
,First_Name,Last_name from MES_TABLE ;

and then again create trigger .....

and it's working now ..

but i don't know why db2 was not calling the before trigger after each
insert . the interesting thing is that db2 has called the after insert
triggers.

yogesh



Seibert, Dave wrote:

>Hello Yogesh,
>
> Can you use the Sequence object to create your Unique ids? Sequences

>solve most of the problems with the Identity feature.
>
>With them you don't need a Stored procedure or trigger or complex
>program logic to create unique numeric values.
>
>See
>http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7p
>ubs.d
>2w/en_main
>
>and select the SQL reference or Admin guide for information about
>sequences.
>
>
>David Seibert
>Compuware Corporation Database Product Architect
>[login to unmask email]
>
>
>
>
>
>
>The contents of this e-mail are intended for the named addressee only.
>It contains information that may be confidential. Unless you are the
>named addressee or an authorized designee, you may not copy or use it,
>or disclose it to anyone else. If you received it in error please
>notify us immediately and then destroy it.
>
>
>
>visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
>the list can
>
>
>

--
Yogesh Kansal
Davlin Software Pvt Ltd .
2nd Floor , Punja Building,
Lalbaugh , Mangalore
Ph 0824-451001 ext - 19

"For every problem there is always a solution that is simple, obvious,
and wrong !! "



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Peter Backlund

Re: Insert prob
(in response to Isaac Yassin)
As I understand it, when the construction is of the type
insert into ... select ...
and the select produces multiple rows, the before trigger is called
once for each row in the select and then the after trigger is called
once for each row in the select. As the idgen table was updated
in the after trigger ( can not be done someplace else), the id
was the same for all rows resulting in PK-violations.

Peter

Isaac Yassin wrote:

>Hi,
>
>I guess I have some understanding problem
><snip>
>why db2 was not calling the before trigger after each insert
></snip>
>As far as I know "before trigger" is called before the action, not after
>it :-)
>
>Or is it mis-typing ?
>
>Isaac Yassin
>DBMS & IT Consultant
>IBM Certified Solution Expert
> DB2 V7.1 Database Administration for OS/390
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]
>On Behalf Of yogesh kansal
>Sent: Monday, January 13, 2003 3:41 PM
>To: [login to unmask email]
>Subject: Re: Insert prob
>
>
>hi david
>Thanks for reply .
>The prob is that coding is done and i can't change the id gen tables to
>sequences ..... i have done this ...
>
>
>
>drop trigger trigname ;
>
>insert into tablename (PERSON_ID,FIRST_NAME,LAST_NAME)
>select 'PP'||right('0000000'||rtrim(char(rownumber() over() +1+ (
>select id from PER_PRO_IDGEN))),8)
> ,First_Name,Last_name from MES_TABLE ;
>
>and then again create trigger .....
>
>and it's working now ..
>
>but i don't know why db2 was not calling the before trigger after each
>insert . the interesting thing is that db2 has called the after insert
>triggers.
>
>yogesh
>
>
>
>Seibert, Dave wrote:
>
>
>
>>Hello Yogesh,
>>
>> Can you use the Sequence object to create your Unique ids? Sequences
>>
>>
>
>
>
>>solve most of the problems with the Identity feature.
>>
>>With them you don't need a Stored procedure or trigger or complex
>>program logic to create unique numeric values.
>>
>>See
>>http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7p
>>ubs.d
>>2w/en_main
>>
>>and select the SQL reference or Admin guide for information about
>>sequences.
>>
>>
>>David Seibert
>>Compuware Corporation Database Product Architect
>>[login to unmask email]
>>
>>
>>
>>
>>
>>
>>The contents of this e-mail are intended for the named addressee only.
>>It contains information that may be confidential. Unless you are the
>>named addressee or an authorized designee, you may not copy or use it,
>>or disclose it to anyone else. If you received it in error please
>>notify us immediately and then destroy it.
>>
>>
>>
>>visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
>>the list can
>>
>>
>>
>>
>>
>
>--
>Yogesh Kansal
>Davlin Software Pvt Ltd .
>2nd Floor , Punja Building,
>Lalbaugh , Mangalore
>Ph 0824-451001 ext - 19
>
>"For every problem there is always a solution that is simple, obvious,
>and wrong !! "
>
>
>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can
>
>
>
>
>
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



Isaac Yassin

Re: Insert prob
(in response to Peter Backlund)
Hi Peter ,
You miss-understood me (or I did not write clear enough)
What I meant is that the sentence syntax was wrong.
The BEFORE TRIGGER is not called AFTER the action ...

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Peter Backlund
Sent: Wednesday, January 15, 2003 4:13 PM
To: [login to unmask email]
Subject: Re: Insert prob

As I understand it, when the construction is of the type
insert into ... select ...
and the select produces multiple rows, the before trigger is called
once for each row in the select and then the after trigger is called
once for each row in the select. As the idgen table was updated
in the after trigger ( can not be done someplace else), the id
was the same for all rows resulting in PK-violations.

Peter

Isaac Yassin wrote:

>Hi,
>
>I guess I have some understanding problem
><snip>
>why db2 was not calling the before trigger after each insert
></snip>
>As far as I know "before trigger" is called before the action, not
after
>it :-)
>
>Or is it mis-typing ?
>
>Isaac Yassin
>DBMS & IT Consultant
>IBM Certified Solution Expert
> DB2 V7.1 Database Administration for OS/390
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]
>On Behalf Of yogesh kansal
>Sent: Monday, January 13, 2003 3:41 PM
>To: [login to unmask email]
>Subject: Re: Insert prob
>
>
>hi david
>Thanks for reply .
>The prob is that coding is done and i can't change the id gen tables to
>sequences ..... i have done this ...
>
>
>
>drop trigger trigname ;
>
>insert into tablename (PERSON_ID,FIRST_NAME,LAST_NAME)
>select 'PP'||right('0000000'||rtrim(char(rownumber() over() +1+ (
>select id from PER_PRO_IDGEN))),8)
> ,First_Name,Last_name from MES_TABLE ;
>
>and then again create trigger .....
>
>and it's working now ..
>
>but i don't know why db2 was not calling the before trigger after each
>insert . the interesting thing is that db2 has called the after insert
>triggers.
>
>yogesh
>
>
>
>Seibert, Dave wrote:
>
>
>
>>Hello Yogesh,
>>
>> Can you use the Sequence object to create your Unique ids? Sequences
>>
>>
>
>
>
>>solve most of the problems with the Identity feature.
>>
>>With them you don't need a Stored procedure or trigger or complex
>>program logic to create unique numeric values.
>>
>>See
>>http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7
p
>>ubs.d
>>2w/en_main
>>
>>and select the SQL reference or Admin guide for information about
>>sequences.
>>
>>
>>David Seibert
>>Compuware Corporation Database Product Architect
>>[login to unmask email]
>>
>>
>>
>>
>>
>>
>>The contents of this e-mail are intended for the named addressee only.
>>It contains information that may be confidential. Unless you are the
>>named addressee or an authorized designee, you may not copy or use it,
>>or disclose it to anyone else. If you received it in error please
>>notify us immediately and then destroy it.
>>
>>
>>
>>visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
>>the list can
>>
>>
>>
>>
>>
>
>--
>Yogesh Kansal
>Davlin Software Pvt Ltd .
>2nd Floor , Punja Building,
>Lalbaugh , Mangalore
>Ph 0824-451001 ext - 19
>
>"For every problem there is always a solution that is simple, obvious,
>and wrong !! "
>
>
>
visit
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the
list
>can
>
>
>
visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can
>
>
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can