How to solve error in a mutating table (Oracle)?

Question

I create the following tables:

create table products

(
        code             varchar(9),
        group_code       varchar(9),
        price            number,  

CONSTRAINT pk_code PRIMARY KEY (code)
);

I created a trigger , for that the price per group of products does not exceed 100 dollars:

create or replace trigger nomore100
before insert or update on products
for each row
declare
cursor c_total is
select group_code, sum(price) as total_price
from products
where group_code=:new.group_code
group by group_code;
v_total c_total%rowtype;
begin
for v_total in c_total loop
    if v_total.total_price+:new.price > 100 then
        raise_application_error(-20150,'No more than 100 dollars');
    end if;
end loop;
end nomore100;
/

The trigger works for inserts, but when i try to do a update:

update products set price=120 where code='PX1';

Oracle return:

"table %s.%s is mutating, trigger/function may not see it"

Thanks for any suggestions or answers, have a nice day!

@ kordirko this question is similar to the question you mentioned however that answer is not complete — SSD
– SSD, Commented May 23, 2016 at 16:53

Muhammad Faisal · Accepted Answer · 2023-04-07 19:04:21Z

0

You can use the pragma autonomous_transaction in the declare section to avoid the table mutating error.

declare pragma autonomous_transaction; -- add this line to enable autonomous transaction

begin //code End;

answered Apr 7, 2023 at 19:04

Muhammad Faisal

641 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Houssin Boulla · Accepted Answer · 2023-08-28 11:34:49Z

Oracle does not allow you to perform such operations, as it can lead to inconsistent data or infinite loops.

To work around this issue, you can use a compound trigger or a combination of before and after triggers. Here's an example of how you can modify your trigger using a combination of before and after triggers:

-- Create a before update trigger to store the old price
CREATE OR REPLACE TRIGGER store_old_price
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  :old.price := :new.price;
END;
/

-- Create an after update trigger to enforce the price limit
CREATE OR REPLACE TRIGGER nomore100
AFTER UPDATE ON products
FOR EACH ROW
DECLARE
  v_total NUMBER;
BEGIN
  SELECT SUM(price) INTO v_total
  FROM products
  WHERE group_code = :new.group_code;

  IF v_total > 100 THEN
    raise_application_error(-20150, 'No more than 100 dollars');
  END IF;
END;
/

With this approach, the "store_old_price" trigger captures the old price before the update, and the "nomore100" trigger checks the total price after the update to enforce the limit. This way, you avoid the mutating table error.

Now, when you execute the update statement:

UPDATE products SET price = 120 WHERE code = 'PX1';

The triggers should work without error, and they will ensure that the total price per group does not exceed 100 dollars.

Collectives™ on Stack Overflow

How to solve error in a mutating table (Oracle)?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related