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%20Duemesnil's gravatar image

Thomas Dueme...
2.4k203357
accept rate: 16%

edited 15 Mar '13, 18:12

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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.

permanent link

answered 02 Feb '10, 14:46

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Comment Text Removed

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.

(03 Feb '10, 11:05) Thomas Dueme...

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 !

permanent link

answered 02 Feb '10, 12:24

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.4k203357
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×113
×95
×58

question asked: 02 Feb '10, 12:19

question was seen: 3,131 times

last updated: 15 Mar '13, 18:12