What fails in this row level trigger?

Question

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.

Answer 1

Just so you know: null + 123 = null

I guess that explains it. Initialize the total to 0 and everything should work.

Edit

You can do it like this:

if inserting then
  dbms_output.put_line(' On Insert');
  update fornecimento f set f.total_enc_fornec = nvl(f.total_enc_fornec, 0) +: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 = nvl(f.total_enc_fornec, 0) - :OLD.prec_total_if +:NEW.prec_total_if  where f.id_fornecimento = :NEW.id_fornecimento;

end if;

Answer 2

Your trigger will fail in a multi-user environment, unless you implement some kind of serialization at the parent level.

If two sessions at around the same time update different rows in item_fornecimento which have the same id_fornecimento, the fornecimento.total_enc_fornec will be incorrectly updated, because each session will not see uncommitted changes made by other sessions.

To solve this you would probably code your application to attempt an exclusive lock on the parent fornecimento record prior to updating/inserting the items for it.