Since we upgraded our Database from 9.0.2. to 10.0.1. one of my trigger constructs is broken.
When a user changes a value in a purchase order line (testchild) the purchase order (testmain) record was updated in version 9.0.2.
In 10.0.1 this updated is skipped.
create table testmain (
PO_ID char(12) not null,
PO_GOODSNET numeric(12,4) not null default 0,
constraint PK_TESTMAIN primary key (PO_ID)
);
create table testchild (
POLINE_ID char(12) not null,
PO_ID char(12) not null,
POLINE_PRICE numeric(12,4) not null,
POLINE_QUANTITY numeric(12,4) not null,
POLINE_SUM numeric(12,4) not null default 0,
constraint PK_TESTCHILD primary key (POLINE_ID)
);
create TRIGGER "testchild_CRNCY_TBIU" BEFORE INSERT, UPDATE
ORDER 1 ON "testchild"
REFERENCING NEW AS NewRow
FOR EACH ROW
-- calculates the sums of a line and convert the price and the sum
into the system lead currency
BEGIN
set NewRow.POLINE_SUM = NewRow.POLINE_PRICE * NewRow.POLINE_QUANTITY;
END;
create trigger "testchild_UpdateMain" AFTER insert, update, delete
order 99
ON "testchild"
referencing new as NewRow old as OldRow
for each row
-- Update corresponding denormalized purchase order columns
begin
declare cPO_ID REPL_ID;
Declare nSum Amount;
if inserting or updating then
set cPO_ID = NewRow.PO_ID;
else
set cPO_ID = OldRow.PO_ID;
end if;
-- Update PO_GOODSNET
if deleting or inserting or UPDATING('POLINE_SUM') then
select sum(POLINE_SUM)
into nSum
from testchild
where PO_ID = cPO_ID;
update testmain
set PO_GOODSNET = nSum
where PO_ID = cPO_ID;
end if;
end;
TEST CASE
insert into testmain ( PO_ID ) values ( 1 );
insert into testchild( POLINE_ID, PO_ID, POLINE_PRICE, POLINE_QUANTITY )
values ( 'CHILD1', 1, 1.50, 1000 ) ;
update testchild set POLINE_PRICE = 1 where POLINE_ID = 'CHILD1';
asked
02 Feb '10, 12:19
Thomas Dueme...
2.7k●28●39●65
accept rate:
17%