By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

help request

P: n/a
Hi to all NG

Sorry for my bad English,
I write a peace of code in trigger. more in detail I write a select, in
order to check th primary key in another table and if the record is present
I issue an update in other case I issue an insert.

I have got a book that tolking about create trigger, with some examples but
I can't find the good information. So I don't understand how to declare a
variable in the trigger in order to use a select 1 into :variable from
table1 and so on

Someone may I indicate some book to read or some example to watch or other
type of suggest ?
best thks in advance
Sergio
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Again me.

in order to explain better I try this examples but it does not work.
Create Trigger ITSO4710/InsertOrderHeaderTotal

After Insert on ITSO4710/ORDER_DETAIL

Referencing NEW_TABLE as NewDetail

For Each Statement

Mode DB2SQL

Set Option DbgView = *SOURCE

BEGIN ATOMIC

Declare SumTotal Decimal(7, 0);

select coalesce(sum(NewDetail.OrderDtl_Total), 0)

into SumTotal

from NewDetail;

update Order_Header

set Order_Total = Order_Total + SumTotal ;

END;

Where is the problem ????

"eap90210" <ea******@yahoo.it> ha scritto nel messaggio news:dh**********@stargate1.inet.it...
Hi to all NG

Sorry for my bad English,
I write a peace of code in trigger. more in detail I write a select, in
order to check th primary key in another table and if the record is present
I issue an update in other case I issue an insert.

I have got a book that tolking about create trigger, with some examples but
I can't find the good information. So I don't understand how to declare a
variable in the trigger in order to use a select 1 into :variable from
table1 and so on

Someone may I indicate some book to read or some example to watch or other
type of suggest ?


best thks in advance
Sergio

Nov 12 '05 #2

P: n/a
eap90210 wrote:
Again me.

in order to explain better I try this examples but it does not work.

Create Trigger ITSO4710/InsertOrderHeaderTotal

After Insert on ITSO4710/ORDER_DETAIL

Referencing NEW_TABLE as NewDetail

For Each Statement

Mode DB2SQL

Set Option DbgView = *SOURCE

BEGIN ATOMIC

Declare SumTotal Decimal(7, 0);

select coalesce(sum(NewDetail.OrderDtl_Total), 0)

into SumTotal

from NewDetail;

update Order_Header

set Order_Total = Order_Total + SumTotal ;

END;

Where is the problem ????

"eap90210" <ea******@yahoo.it <mailto:ea******@yahoo.it>> ha scritto nel
messaggio news:dh**********@stargate1.inet.it...
> Hi to all NG
>
> Sorry for my bad English,
> I write a peace of code in trigger. more in detail I write a select, in
> order to check th primary key in another table and if the record is

present
> I issue an update in other case I issue an insert.
>
> I have got a book that tolking about create trigger, with some

examples but
> I can't find the good information. So I don't understand how to

declare a
> variable in the trigger in order to use a select 1 into :variable from
> table1 and so on
>
> Someone may I indicate some book to read or some example to watch or

other
> type of suggest ?

In triggers DB2 for LUW supports the SET statement, but not SELECT INTO
or VALUES INTO.
Either way, please take a look at the MERGE statement. It is meant to
solve "if not exist INSERT otherwise UPDATE" problems.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
thanks
Cheers
Sergio
"Serge Rielau" <sr*****@ca.ibm.com> ha scritto nel messaggio
news:3p************@individual.net...
eap90210 wrote:
Again me.
in order to explain better I try this examples but it does not work.

Create Trigger ITSO4710/InsertOrderHeaderTotal

After Insert on ITSO4710/ORDER_DETAIL

Referencing NEW_TABLE as NewDetail

For Each Statement

Mode DB2SQL

Set Option DbgView = *SOURCE

BEGIN ATOMIC

Declare SumTotal Decimal(7, 0);

select coalesce(sum(NewDetail.OrderDtl_Total), 0)

into SumTotal

from NewDetail;

update Order_Header

set Order_Total = Order_Total + SumTotal ;

END;

Where is the problem ????

"eap90210" <ea******@yahoo.it <mailto:ea******@yahoo.it>> ha scritto nel
messaggio news:dh**********@stargate1.inet.it...
> Hi to all NG
>
> Sorry for my bad English,
> I write a peace of code in trigger. more in detail I write a select,

in
> order to check th primary key in another table and if the record is

present
> I issue an update in other case I issue an insert.
>
> I have got a book that tolking about create trigger, with some

examples but
> I can't find the good information. So I don't understand how to

declare a
> variable in the trigger in order to use a select 1 into :variable from
> table1 and so on
>
> Someone may I indicate some book to read or some example to watch or

other
> type of suggest ?

In triggers DB2 for LUW supports the SET statement, but not SELECT INTO or
VALUES INTO.
Either way, please take a look at the MERGE statement. It is meant to
solve "if not exist INSERT otherwise UPDATE" problems.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.