Trouble with Triggers

I have Oracle 9i running on a Windows machine. I have a an application that talks to Oracle through the OCI. In the course of processing I update a row in a table. So far so good.

I recently created a trigger on the table:

TriggerStats after update on Trade for each row begin

    update Trade set column1 = column3 - column2;

• etc...
• column3 is new and column2 is old
• tried all sorts of variants: select :new.column3 - old:column2 into :new.column1 from DUAL; -- this required changing "after" --"before" :new.column1 := new.column3 - old:column2 ;
• also tried :new.column1 := new.column3 - old:column2 ; end;

The table updates correctly when the trigger is disabled Of course column3 is not properly set, then.

BUT The update does not occur when the trigger is enabled. Obviously column3 is not updated either.

What am I doing wrong? Received on Mon Oct 14 2002 - 23:51:00 CDT