on 2010 Feb 02 12:19 PM
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;
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';
Request clarification before answering.
This problem was introduced in the GA release of 10.0.0 and was fixed in build 3874 of the 10.0.1 release. I apologize for the inconvenience.
Once you apply an EBF of 10.0.1 with build 3874 or later, your BEFORE
and AFTER
triggers should work as before.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The problem is easily fixed when the update to the row is directly done with the BEFORE TRIGGER.
But anyway there is a behaviour change in SA10.
Changes done to a field by a Trigger no longer forces a After Trigger to execute on that change !
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.