oracle trigger after inserting or udpating a sales item

Question

Hi there,

I have this table that represents a weak entity and is a typical table for introducing items ordered: insert into ITEM_FORNECIMENTO values (a_orderId,a_prodId,a_prodQtd, a_buyPrice);

I want my trigger to update the last column (the total price of products WITHOUT iva) to do this : totalPrice= totalPrice*(1+(iva/100), each time I insert or update an entry on that table.

so, I came up with this, but I'm totally wrong when it comes to work with new and old values.

create or replace
trigger t_replaceTotal
after insert or update of id_prod,qtd_if,prec_total_if on item_fornecimento
for each row 

declare
iva produto.iva_prod%type;
idProd  produto.id_prod%type;
r_old item_fornecimento.prec_total_if%type:=null;
r_new item_fornecimento.prec_total_if%type:=null;

begin
  select iva_prod,id_prod into iva,idprod from produto p where p.id_prod = id_prod; 
  r_old:= :old.prec_total_if;
  r_new:= :new.prec_total_if;
  update item_fornecimento item set prec_total_if = r_old * (1+(iva/100)) where item.id_prod = idprod;
end;

Could someone please help rewriting this code? I'm getting the error: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "FUSION.T_REPLACETOTAL", line 8 ORA-04088: error during execution of trigger 'FUSION.T_REPLACETOTAL'

Answer 1

That error is telling you that a query that saves its result into a variable is returning more than one result. As such it doesn't know what you want to save in the variable.

Try running the following:

select iva_prod,id_prod from produto p where p.id_prod = id_prod;

and I bet it will give you more than one result, which it can't save into iva,idprod.

Answer 2

The problem is that you are trying to fetch every row from the table because you are matching a column (id_prod) to itself. I suspect you want to use :new.id_prod or :old.id_prod.

select .... from produto p where p.id_prod = id_prod; 

Next, make this a BEFORE INSERT/UPDATE trigger and replace the UPDATE statement with

 :new.prec_total_if := r_old * (1+(iva/100));

Otherwise you'll get a mess of mutating table errors.