3

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.

The error is:

error ORA-04091: table USUARIO.LINEAS is mutating, trigger/function may not see it ORA-06512: at "USUARIO.TRG_ACTUALIZARPEDIDO", line 5 ORA-04088: error during execution of trigger 'USUARIO.TRG_ACTUALIZARPEDIDO'

I've learning about it, but can't find a solution or understand by myself what's the problem.

CREATE OR REPLACE TRIGGER trg_actualizarpedido
AFTER INSERT OR UPDATE OR DELETE ON LINEAS
FOR EACH ROW

DECLARE
    sumaImporteLineas NUMBER := 0;

BEGIN
    SELECT SUM(IMPORTE) INTO sumaImporteLineas FROM LINEAS WHERE NUMPEDIDO = :NEW.NUMPEDIDO;
    UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas WHERE ped.NUM = :NEW.NUMPEDIDO;
END;

Thanks!

  • You can't SELECT ... FROM LINEAS within a FOR EACH ROW trigger on LINEAS. – Michael Kutz Apr 23 at 19:22
  • 2
    In addition to what @MichaelKutz said, you should not store in the database something that can be calculated by querying it (such as the total of order items). – mustaccio Apr 23 at 19:41
1

ORA-04091

This is Oracle's way of saying:

you really don't want to do that.

Here is my short-list of workarounds.

Option 1

Use a VIEW.

This is the preferred method.

Option 2

Use a Materialized View.

Yes, you can Fast Refresh SUM() on commit.

Option 3

Use a TAPI (Table API). (a Package)

All DML operations must go through this Package; never against the actual table.

Option 4

3-Trigger solution. (This could be a single Complex trigger.)

Without proper locking, you could have "lost updates".

Even with proper locking, Dead Locks could occur.

0

Within a trigger reacting to a change to a table, you cannot query the table itself. But you don't need to.

It looks as though you're trying to keep a running total based on changes to the value of IMPORTE. For that, you need not one but three triggers:

/* Insert - Add New value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido1
AFTER INSERT ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL + :NEW.IMPORTE 
    WHERE NUMPEDIDO = :NEW.NUMPEDIDO ; 
END;

/* Update - Subtract the Old value, Add New value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido2
AFTER UPDATE ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL - :OLD.IMPORTE + :NEW.IMPORTE 
    WHERE NUMPEDIDO = :NEW.NUMPEDIDO ; 
END;

/* Delete - Subtract the Old value */ 
CREATE OR REPLACE TRIGGER trg_actualizarpedido3
AFTER DELETE ON LINEAS
FOR EACH ROW
BEGIN
    UPDATE PEDIDOS 
    SET TOTAL = TOTAL - :OLD.IMPORTE 
    WHERE NUMPEDIDO = :OLD.NUMPEDIDO ; 
END;
  • You will get a problem if TOTAL or value of IMPORTE is NULL – Wernfried Domscheit Apr 24 at 18:56
0

As already given in the other answers using a trigger for such task is not the preferred way of doing it. For the sake of completeness here a trigger based solution (not tested):

create or replace trigger trg_actualizarpedido
  for insert or update or delete on LINEAS 
  COMPOUND TRIGGER

  type NUMPEDIDO_t is table of LINEAS.NUMPEDIDO%type;
  NUMPEDIDOs NUMPEDIDO_t ;
  sumaImporteLineas NUMBER;

  before statement is 
  begin
    NUMPEDIDOs := NUMPEDIDO_t();
  end before statement;

  after each row is 
  begin
    NUMPEDIDOs.EXTEND;
    IF INSERTING OR UPDATING ('NUMPEDIDO') THEN
       NUMPEDIDOs(NUMPEDIDOs.LAST) := :NEW.NUMPEDIDO;
    ELSIF DELETING THEN
       NUMPEDIDOs(NUMPEDIDOs.LAST) := :OLD.NUMPEDIDO;
    END IF;
  end after each row;

  after statement is 
  begin      
    for i in NUMPEDIDOs.FIRST..NUMPEDIDOs.LAST LOOP
       SELECT SUM(IMPORTE) INTO sumaImporteLineas 
       FROM LINEAS 
       WHERE NUMPEDIDO = NUMPEDIDOs(i);

       UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas 
       WHERE ped.NUM = NUMPEDIDOs(i);
    end loop;          
  end after statement;

end;
-1

You can not query the table that caused a trigger to fire inside the trigger itself. But in this case, I don't see any need to. You can use :NEW to access the values you're selecting from the table instead:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.