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?

3 answers

  • answered 2018-10-11 19:42 Gordon Linoff

    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;
    

  • answered 2018-10-11 19:53 Abdullah Ahsan

    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;
    

  • answered 2018-10-11 20:10 Giovanni Esposito

    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;