mutating table in a before trigger

Question

I am trying to use a trigger to avoid a deletion of a row in table Products under some circumstances. The code is the following

CREATE TRIGGER trgPreventProductRemoval
    BEFORE DELETE ON Products
    FOR EACH ROW

    BEGIN
        DECLARE
            l_custid INTEGER;
        BEGIN
            SELECT count(*) INTO l_custid FROM Orders WHERE product = :old.prodDescription ;

            IF l_custid > 0 THEN
                raise_application_error (-20100, 'You can not delete a product that has active orders!');
            END IF;
        END;
    END;

However I get the error: table ORDERS is mutating, trigger/function may not see it.

How can I fix it?

EDIT-SOLUTION: I have accepted the solution below because it is the "politically correct". Due to some "restrictions" I was unable to use it and finally found a different workaround. See the solution that I have posted seperately.

possible duplicate of [ORA-04091: table \[blah\] is mutating, trigger/function may not see it](http://stackoverflow.com/questions/375968/ora-04091-table-blah-is-mutating-trigger-function-may-not-see-it) — Sathyajith Bhat, Jan 03 '12 at 04:15

score 5 · Accepted Answer · answered Jan 02 '12 at 18:28

5

Use a foreign key between Orders and Product table instead of a trigger.

answered Jan 02 '12 at 18:28

Shannon Severance

18,025
3
46
67

This would be the cleaner solution but I am not allowed to do it. – niels Jan 02 '12 at 18:34
4

Not allowed? That seems a bit ridiculous. The whole purpose of a foreign key is to solve *exactly* the problem you're facing. I understand that you are the "victim" here, of a really, exceedingly, bad policy. But, given the race conditions and locking issues you're certain to run into, you may actually be better off fighting the political battle to be able to use foreign keys, than trying to code around it. – Mark J. Bobak Jan 02 '12 at 18:51
@Mark J. Bobak: I am doing H/W and I MUST do it with triggers because I was ASKED to do it that way. If I had to choose I would certainly do it with fkeys. – niels Jan 02 '12 at 18:57
We should really have hw.stackexchange.com :) – kubanczyk Jan 02 '12 at 21:19
Um, ok, I'll bite...what's "H/W"? – Mark J. Bobak Jan 03 '12 at 05:46
I am accepting this answer since its "politically correct", although I managed my way through with another way. – niels Jan 04 '12 at 17:40

score 0 · Answer 2 · answered Jan 02 '12 at 18:12

0

Try to use AUTONOMOUS_TRANSACTIONS :

http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm#i7733

answered Jan 02 '12 at 18:12

rkosegi

14,165
5
50
83

Ben · Answer 3 · 2012-01-02T19:30:28.327

You can't fix this error, as the table you're selecting from is in the middle of a DML operation in the session you're in so there's no way to be certain what the answer to your query is at the time you're making it.

There is, however, a slightly messy but easy way round the problem:

Create a view of the form select * from products.
Compile the trigger on the view not the table.
Ensure the trigger performs the DML operations you actually intended for the table (the messy part).
Only perform DML operations on the view not the table.

So, something like this should work.

create or replace view v_products as select * from products;

CREATE TRIGGER trgPreventProductRemoval
    BEFORE DELETE ON v_products
    FOR EACH ROW

    DECLARE

    l_custid INTEGER;

    BEGIN

       SELECT count(*) INTO l_custid 
         FROM Orders 
        WHERE product = :old.prodDescription;

        IF l_custid > 0 THEN
         raise_application_error (-20100, 'You can not delete a product that has active orders!');
        END IF;

       -- assumed, I don-t know the actual columns.
        delete from products where product = :new.product_id;

    END trgPreventProductRemoval;

@niels that'd be because I forgot to add a `declare` - I've edited the answer; I deduced Oracle from the syntax and this is all Oracle so don't worry. However, @shannon's solution, in this particular case, is actually a lot better than this little hack of mine. — Ben, Jan 02 '12 at 19:32
This wouldn't work because Oracle doesn't allow a "before" trigger on views. It doesn't matter though because I have found a workaround that works for the specific database that I am working on (see my posted solution). Thank you for your concern. — niels, Jan 02 '12 at 19:43

niels · Answer 4 · 2012-01-04T17:38:05.657

The problem is finally solved by removing the "link" between Products and Orders. The link was a ON DELETE CASCADE in a foreign key of an intermediate table called SuplProd. By removing the ON DELETE CASCADE from the fk of SuplProd the table Orders became non-mutating.

To leverage for the removal of ON DELETE CASCADE, I just added one more line of code in the trigger's code, just after the END IF;

So, technically, this workaround works by making the necessary table non-mutating.

mutating table in a before trigger

4 Answers4