Oracle trigger depending on virtual column produces wrong data

Question

I have this trigger

create or replace trigger upd_totprc_trg
after insert or update or delete of total_price on sales_detail
for each row
declare
 v_diff   number := 0;
 v_master number;
begin

 if inserting then
  v_master := :new.master_id;
  v_diff   := :new.total_price;
elsif updating then
  v_master := :new.master_id;
  v_diff   := :new.total_price - :old.total_price;
elsif deleting then
  v_master := :old.master_id;
  v_diff   := :old.total_price * -1;
end if;

update sales_master
 set value = value + v_diff
 where id = v_master;
end;
/

it calculates the changes in sales_detail.total_price and updates the sales_master.value.

the thing is that the total_price column is a virtual column defined as the product of quantity and unit_price. When I enter new detail row the trigger seems not to catch the new calculated value even though it's an after. My question is: does the execution of the virtual column expression conflicts with the running of the trigger? note that when I set the total_price as a normal column again it returned correct values.

I am using Oracle 12c

score 0 · Answer 1 · answered Nov 11 '16 at 17:43

Your trigger is for update, insert or delete on virtual column. But this column is not inserted or updated. It is calculated when someone request data. So not triggering the triogger is correct value. What more it is not allowed to update or insert virtual column. ORA-54017: UPDATE operation disallowed on virtual columns will be produced when trying to directly update virtual column.
What you need is trigger on columns that are base for calculating virtual one (quantity and unit_price). This is obvious logic that when someone change base values result will also be changed.

Oracle trigger depending on virtual column produces wrong data

1 Answers1