Triggering column after update issue
I got a problem with a trigger I'm trying to implement into my program.
I got a table called Products it has columns as ID, PRICE and DISCOUNT
I made this trigger:
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER after update of DISCOUNT on PRODUCTS for each row BEGIN update PRODUCTS set PRICE = PRICE * (1 - DISCOUNT); END;
I want to change the price column of the row after an update of discount column.
For example if I execute this query:
UPDATE PRODUCTS SET DISCOUNT = 25 WHERE ID = 25;
When I execute that query I'm getting the following error:
Error starting at line : 11 in command - update products set DISCOUNT = 25 where id = 1 Error report - ORA-04091: table YASSINEII.PRODUCTS is mutating, trigger/function may not see it ORA-06512: at "YASSINEII.DISCOUNT_TRIGGER", line 2 ORA-04088: error during execution of trigger 'YASSINEII.DISCOUNT_TRIGGER'
Any idea what im doing wrong here?
I am guessing you really want a "before update" trigger that updates values in a single row. Something like:
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER before update of DISCOUNT on PRODUCTS for each row BEGIN :new.price := :new.price * ( 1 - :new.korting_percentage); END;
You can't 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
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER after update of DISCOUNT on PRODUCTS for each row BEGIN update PRODUCTS set :new.PRICE = :new.PRICE * (1 - korting_percentage); END;
You can avoid mutating table exception using compound triggers.
Here the example (remember to change the table and the columns by which they fit your data model):
CREATE or REPLACE TRIGGER DISCOUNT_TRIGGER FOR UPDATE of DISCOUNT on PRODUCTS COMPOUND TRIGGER BEFORE STATEMENT is BEGIN UPDATE your_table SET your_column = your_value WHERE your_condition; END BEFORE STATEMENT; --BEFORE EACH ROW is --BEGIN --Put here the statement --END BEFORE EACH ROW; --AFTER STATEMENT is --BEGIN --Put here the statement --END AFTER STATEMENT; --AFTER EACH ROW is --BEGIN --Put here the statement --END AFTER EACH ROW; END;