CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
J. Adam
J. Adam

Reputation: 1641

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?

Upvotes: 0

Views: 41

Answers (3)

Giovanni Esposito
Giovanni Esposito

Reputation: 74

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;

Upvotes: 0

Abdullah Ahsan
Abdullah Ahsan

Reputation: 107

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 1

Related Questions