Mutating tables and foreign keys
The mutating table error often arises in the context of foreign keys. Let's take a look at an example.
The staff at Humongous Bank have implemented their transaction system using Version 7.3.4 of Oracle, and they are feeling very magnanimous: they have decided to give away $100 to any customer who opens up a new account. As usual, they neglected to inform the applications team until the weekend before the promotion begins; thus poor Kay Fourohone is asked to work through the weekend to implement the new system.
Kay applies her stellar logic to the situation—every time an account is opened, a deposit of $100 must be made into my . . . I mean, the new account. This is a perfect application of DML triggers: every time an INSERT is performed on the account table, a trigger will put the deposit entry into the account transaction table. Kay is extremely happy because such a simple trigger will only take half a day to code, debug, test, and implement, leaving her the rest of the weekend to practice bowling before the league finals on Monday.
Knowing that the tables involved are account and account_transaction and that there is a foreign key between them on the account_id field, Kay creates the following trigger:
CREATE TRIGGER give_away_free_moneyAFTER INSERT ON accountFOR EACH ROWBEGIN INSERT INTO account_transaction (transaction_id, account_id, transaction_type, transaction_amount, comments) VALUES(account_transaction_seq.nextval, :NEW.account_id, 'DEP', 100, 'Free Money!');END;Kay is so confident that this will work that she almost doesn't bother to test it. What could go wrong with such a simple chunk of code? She quickly gets her answer.
SQL> INSERT INTO account 2 (account_id,account_owner) 3 VALUES(1,'Test'); INSERT INTO account *ERROR at line 1:ORA-04091: table SCOTT.ACCOUNT is mutating, trigger/function may not see itORA-06512: at "SCOTT.GIVE_AWAY_FREE_MONEY", line 2ORA-04088: error during execution of trigger 'SCOTT.GIVE_AWAY_FREE_MONEY'The account table is indeed mutating during this transaction as a result of the initial INSERT statement. The trigger then attempts to insert a record into the account_transaction table, which in turn requires Oracle to validate the account_id against the account table. But because foreign key validation occurs before the trigger even fires, Oracle cannot be assured that the entry is valid.
Now you may be saying that this error makes no sense because all of the required information is easily available; the primary key is available from the original INSERT statement. While this is a valid argument, the fact remains that we've exposed a limitation of row-level triggers here.
|
This limitation applies only to row-level triggers; statement-level triggers are immune because they do not process single rows. This immunity is what Kay will exploit to get around her mutation problem.