Error executing a Trigger

0

I'm having the following error when executing my trigger, how could I resolve it?

ERROR

The error when I insert insert into the product-purchase table to calculate total INSERT into purchase_product (CPR_CODIGO, PRO_CODIGO, CPP_QTDE, CPP_PRECO, CPP_DESCONTO)     VALUES (1,2,7,87,0); ORA-04091: the ADMIN.COMPRA_PRODUTO table is mutant; the trigger / function may not be able to find it ORA-06512: under "ADMIN.TRG_TOTAL_COMPRA_PRODUTO", line 3 ORA-04088: Error during trigger execution 'ADMIN.TRG_TOTAL_COMPRA_PRODUTO'

TRIGGER

CREATE OR REPLACE TRIGGER "TRG_TOTAL_COMPRA_PRODUTO" 
    BEFORE INSERT OR DELETE OR UPDATE ON COMPRA_PRODUTO

    FOR EACH ROW
DECLARE  

BEGIN

    UPDATE COMPRA_PRODUTO SET CPP_TOTAL = 
        (
            SELECT ((CPP_TOTAL * CPP_QTDE) - (((CPP_TOTAL * CPP_QTDE) * CPP_DESCONTO) / 100)) FROM COMPRA_PRODUTO
                WHERE CPP_CODIGO = :NEW.CPP_CODIGO AND
                        PRO_CODIGO = :NEW.PRO_CODIGO
        ) 
    WHERE CPR_CODIGO = :NEW.CPR_CODIGO;

END;

Insert

INSERT INTO COMPRA_PRODUTO (CPR_CODIGO, PRO_CODIGO, CPP_QTDE, CPP_PRECO, CPP_DESCONTO) 
    VALUES (1,3,9,90, 0);

Database Relation

    
asked by anonymous 10.09.2018 / 21:49

1 answer

1

This is the famous case of mutant tables , which are basically triggers that try to query / modify the same table that triggers the trigger, is a standard oracle behavior.

To solve your problem you will need to handle this calculation directly in the insert / update operation or create a procedure that is triggered after performing these operations.

    
11.09.2018 / 14:10