Trigger cant read the table, after being fired by the same table

Question

Lets say I have a table as follows--

create table employees 
(
 eno      number(4) not null primary key, 
 ename    varchar2(30),
 zip      number(5) references zipcodes,
 hdate    date
);

I've created a trigger using the following code block

create or replace TRIGGER COPY_LAST_ONO
AFTER INSERT ON ORDERS
FOR EACH ROW
DECLARE

ID_FROM_ORDER_TABLE VARCHAR2(10);

BEGIN

SELECT MAX(ORDERS.ONO)INTO ID_FROM_ORDER_TABLE from ORDERS ;
DBMS_OUTPUT.PUT_LINE(ID_FROM_ORDER_TABLE);

INSERT INTO BACKUP_ONO VALUES( VALUE1, VALUE2,VALUE3, ID_FROM_ORDER_TABLE);

END;

The trigger fires after insertion and attempts to read from the table that fired it(logically duhh!) but oracle is giving me an error and asking me to modify the trigger so that it doesnt read the table. Error code-

Error report -
SQL Error: ORA-04091: table TEST1.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "TEST1.COPY_LAST_ONO", line 8
ORA-04088: error during execution of trigger 'TEST1.LOG_INSERT'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
       this statement) attempted to look at (or modify) a table that was
       in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

What I'm trying to achieve with this trigger is to copy the last INSERTED ONO (which is a primary key for the ORDER table) immediately to a different table after being INSERTED. What I don't get is, why oracle complaining? The trigger is attempting to read AFTER the insertion!

Ideas? Solution?

MANY THANKS

Does `BACKUP_ONO` table have a `foreign key` to table `ORDERS` on which the trigger is executing? That can cause something like this to happen. You can read more http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm — Jagmag, Dec 18 '15 at 19:40
@InSane no thats not the case. 'BACKUP_ONO' only has a primary key. But there is an attribute with the same name `ONO` as I'm copying it from `ORDERS` to `BACKUP_ONO` — envyM6, Dec 18 '15 at 19:43
Maybe unrelated to your error - but wouldn't it be better to get ONO using :NEW instead of trying to get it via MAX? You probably have ONO in your order that is being inserted? — Jagmag, Dec 18 '15 at 19:44
Hmmm..if there is no FK in BACKUP_ONO, then you have me stumped too :-) — Jagmag, Dec 18 '15 at 19:46

Gary_W · Accepted Answer · 2015-12-18T19:55:32.130

5

If you are trying to log the ONO you just inserted, use :new.ono and skip the select altogether:

INSERT INTO BACKUP_ONO VALUES( VALUE1, VALUE2,VALUE3, :new.ono);

I don't believe you can select from the table you are in the middle of inserting into as the commit has not been issued yet, hence the mutating table error.

P.S. Consider not abbreviating. Make it clear for the next developer and call it ORDER_NUMBER or at least a generally accepted abbreviation like ORDER_NBR, whatever your company's naming standards are. :-)

FYI - If you are updating, you can access :OLD.column as well, the value before the update (of course if the column is not a primary key column).

edited Dec 18 '15 at 19:55

answered Dec 18 '15 at 19:43

Gary_W

9,933
1
22
40

And we have a winner!! So let me get this straight. `:NEW.ONO` OR `NEW.PRIMARY_KEY` returns the very last primary key being inserted? – envyM6 Dec 18 '15 at 19:47
Isn't ONO the primary key? – Gary_W Dec 18 '15 at 19:48
It is, I'm trying to get the syntax right.. Is my understanding correct? – envyM6 Dec 18 '15 at 19:49
:NEW.ONO is what you want. – Gary_W Dec 18 '15 at 19:50
In reply to your `P.S`, I was given a script which creates the table (Uni assignment) so I cant change the script. Wish someone have the lecturer know. – envyM6 Dec 18 '15 at 19:51
A good lesson can still be taken away from this. If you are the developer and come across a poorly named column or variable like this that is beyond your scope to change, make sure you at least add a comment defining what it is to help yourself and future maintainers of the code. We have all had to deal with someone else's bizarre idea of naming conventions. Just be sure to think of the person maintaining after you. It will strengthen your understanding and help the next person at the same time. :-/ – Gary_W Dec 18 '15 at 20:02
How do I do the reverse? How do I know the last PK that has been deleted along with the whole row? Because I am indeed logging the `ORDER` table. so I need to save the `ONO` before I delete it – envyM6 Dec 18 '15 at 20:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98374/discussion-between-envym6-and-gary-w). – envyM6 Dec 18 '15 at 20:06
1

Use a BEFORE DELETE trigger and log the :OLD.column_name values – Gary_W Dec 18 '15 at 20:06
@Gary_W Your comment about being in the middle of a transaction because the table hasnt commited yet was exactly what I needed to understand my trigger mutating table issue. Granted understanding isnt the same as fixing, but its a step. – Fering Aug 01 '19 at 19:05
@Fering Glad this post helped! – Gary_W Aug 01 '19 at 20:59

score 2 · Answer 2 · edited May 23 '17 at 12:30

Amplifying @Gary_W's answer:

Oracle does not allow a row trigger (one with FOR EACH ROW in it) to access the table on which the trigger is defined in any way - you can't issue a SELECT, INSERT, UPDATE, or DELETE against that table from within the trigger or anything it calls (so, no, you can't dodge around this by calling a stored procedure which does the dirty work for you - but good thinking! :-). My understanding is that this is done to prevent what you might call a "trigger loop" - that is, the triggering condition is satisfied and the trigger's PL/SQL block is executed; that block then does something which causes the trigger to be fired again; the trigger's PL/SQL block is invoked; the trigger's code modifies another row; etc, ad infinitum. Generally, this should be taken as a warning that your logic is either really ugly, or you're implementing it in the wrong place. (See here for info on the evil of business logic in triggers). If you find that you really seriously need to do this (I've worked with Oracle and other databases for years - I've really had to do it once - and may Cthulhu have mercy upon my soul :-) you can use a compound trigger which allows you to work around these issues - but seriously, if you're in a hole like this your best option is to re-work the data so you don't have to do this.

Best of luck.

BOB I'm really amazed by your answer! I really am! I had to seek help from daddy google to understand what you are on about! LOL.. but hey the trigger above worked. :) — envyM6, Dec 20 '15 at 20:46
@envyM6 If the trigger above worked, it was only because you drop select statement from it. — erod, Mar 04 '20 at 16:01
@EriksonRodriguez: I suspect that the original comment from envyM6 was referring to the trigger in VidyaPandey's answer, which uses `PRAGMA AUTONOMOUS_TRANSACTION`. — Bob Jarvis - Слава Україні, Mar 04 '20 at 17:37

score 1 · Answer 3 · answered Dec 21 '15 at 15:02

1

Modify your trigger to use PRAGMA AUTONOMOUS_TRANSACTION

create or replace TRIGGER COPY_LAST_ONO
AFTER INSERT ON ORDERS
FOR EACH ROW
DECLARE

    ID_FROM_ORDER_TABLE VARCHAR2(10);
    PRAGMA AUTONOMOUS_TRANSACTION; -- Modification
BEGIN
.
.
.

answered Dec 21 '15 at 15:02

Vidya Pandey

211
2
10

Trigger cant read the table, after being fired by the same table

3 Answers3

Linked