cancel
Showing results for 
Search instead for 
Did you mean: 

Why is after update trigger not fired on changes of a before update trigger SQLA 10

thomas_duemesnil
Participant
5,176

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';

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

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.

thomas_duemesnil
Participant
0 Kudos

Dear Glenn. I had discussed with a local Sybase representative and my understanding was that the 9.0.2 behaviour was a mistake. Thanks for the enlightment.

Answers (1)

Answers (1)

thomas_duemesnil
Participant

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 !