What fails in this row level trigger?

Posted by newba on Stack Overflow See other posts from Stack Overflow or by newba
Published on 2010-01-21T19:40:58Z Indexed on 2010/04/09 0:53 UTC
Read the original article Hit count: 408

Filed under:
|
|

I have this trigger:

create or replace trigger t_calctotal
after insert or update on item_fornecimento
REFERENCING NEW AS NEW OLD AS OLD
for each row

begin

  if inserting then
  dbms_output.put_line(' On Insert');
  update fornecimento f set f.total_enc_fornec = f.total_enc_fornec +:NEW.prec_total_if  where f.id_fornecimento = :NEW.id_fornecimento;

  else
  dbms_output.put_line(' On Update');
  update fornecimento f set f.total_enc_fornec = f.total_enc_fornec - :OLD.prec_total_if +:NEW.prec_total_if  where f.id_fornecimento = :NEW.id_fornecimento;

  end if;

end;

Basically I want to refresh the total value of an order (fornecimento), by suming all the items in item_fornecimento; I have to treat this in a different way, case it's an inserting, case it's an updating. The trigger compiles and all and even worked one time, but it was the only one. I've inserted or updated my prec_total_if in item_fornecimento in sqldeveloper, but the order's (fornecimento) total still not change :(.

If it's important, my f.total_enc_fornec it's null until it's replaced by a value inserted by this triggers; it prints the output, but it seems to fail updating.

© Stack Overflow or respective owner

Related posts about plsql

Related posts about triggers