help request

[eap90210]

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

[eap90210]

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

[Serge Rielau]

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

[eap90210]

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