triggers in oracle

Question

triggers in oracle

Navigation

#1 by (0 votes)

1

I have to build a trigger that when a record is inserted in the table detalle_venta, enter the commission in the commission table, you have to calculate the commission (commission * amount) * 0.05, my trigger compiles, but when I insert a record in sale_detail tells me that: table STORE.DETALLE_SALE is mutating, trigger / function may not see it

CREATE OR REPLACE TRIGGER inserta_comision
    AFTER UPDATE OR INSERT OR DELETE 
    ON detalle_venta
    FOR EACH ROW 

    DECLARE
    vprecio     producto.precio%type;

    BEGIN
    SELECT P.precio 
        INTO vprecio
        FROM detalle_venta D 
        JOIN producto P
        ON ( D.id_producto = P.id_producto );

    IF INSERTING THEN 
        INSERT INTO comision values ( :new.folio_venta, (vprecio*:new.cantidad)*.05, sysdate );

    END IF;
END;
/

oracle

asked by Max Sariñana 24.10.2017 в 18:20

source

1 answer

Jump backwards in Wordpress menu Datepicker Region dd / mm / yyyy MVC [closed]

score 0 · Answer 1

You can not reference detalle_venta in a query within a trigger for that same table. That's why you get the error.

Anyway, your SELECT does not make much sense, and I would probably give you another error if I could run, because it would return too many records.

Without understanding everything at 100%, it is most likely that you simply want to delete the table detalle_venta of the query and rather use directly :new.id_producto in the query:

SELECT P.precio 
INTO vprecio
FROM producto P
WHERE P.id_producto = :new.id_producto;