Stack Overflow
Stack Overflow

Questions tagged [mutating-table]

Mutating table errors generally happen when a trigger tries to reference/modify the same table on which trigger is firing on.

Mutating table/trigger errors are mostly caused when you try to reference/modifies the table on which the trigger is firing.

The error generally refers to a flaw in application logic or data model.

APC explains the data model flaw:

Some highly viewed questions/solutions on this

64 questions
1
vote
1 answer

Trying to read a table on Trigger during update, giving mutation error

Before updating a table I have to insert in another table, but to accomplish that I need information from the table (and other tables) which I'm updating, trying to read my updating table in the trigger is giving an error ERROR at line 1: …
Javier Heisecke
  • 1,162
  • 1
  • 11
  • 28
1
vote
2 answers

PL/SQL Error Happening

Select * from cat; Attempting this
Adam
  • 37
  • 2
  • 8
1
vote
2 answers

trigger mutating error

I have emp table in schema1 and emp_fianl in schema2. emp empid ename estatus 1 abc incomplete 2 xyz complete 3 ifg incomplete 4 mno incomplete Emp_final empid ename estatus 2 xyz complete I have to…
user8592197
  • 77
  • 10
1
vote
1 answer

Simultaneous triggers makes inserts and updates difficult

I have 4 tables in Oracle: hotel, tourist, stay, leave. The stay table relates a tourist staying in a hotel, and the leave table stores the information of the date when a tourist leaves a hotel. CREATE TABLE hotel ( id NUMBER(5), name…
Jorge Ramirez
  • 98
  • 1
  • 7
1
vote
3 answers

Copy row to history table before update without enumerating column names in PL/SQL trigger?

I am trying to create a PL/SQL trigger that copies the current version of a row into a history table when the row is updated. This can be easily done like this: CREATE OR REPLACE TRIGGER foo BEFORE UPDATE ON bar FOR EACH ROW BEGIN INSERT INTO…
Anders
  • 8,307
  • 9
  • 56
  • 88
1
vote
0 answers

Creating an AFTER trigger in Oracle to access :new and :old variables

I have the following situation in a table with two date columns, Start_DT and End_DT. +----+------------+------------+ | ID | Start_DT | End_DT | +----+------------+------------+ | 1 | 01-01-2012 | 02-01-2012 | | 2 | 02-01-2012 | 05-02-2012…
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
1
vote
1 answer

ORACLE Trigger Check Table and delete row

Hey I got a little Problem with creating a trigger at my Database. I have an Table that's named 'located' and contains: Country City Province Lake Sea River So Lake, Sea and River are Foreign Keys. I created these three constraints: ALTER…
dominic
  • 385
  • 1
  • 6
  • 23
1
vote
2 answers

validation using the same table

My tables: parent (id number) child (id number, parent_id number, allocation number) So for every parent, there is set of three fixed records in child table. And, I need to put a validation block to restrict users from updating the allocation for…
Vishal
  • 198
  • 1
  • 3
  • 11
1
vote
3 answers

ORA-04091: table SCMA.XX is mutating, trigger/function may not see it

I have two tables- XX and YY with their triggers calling each other in case of an update. The trigger on XX goes like this: CREATE OR REPLACE TRIGGER SCMA.XX_RBIU BEFORE INSERT OR UPDATE ON SCMA.XX FOR EACH ROW -- PL/SQL BLOCK BEGIN IF…
Rachcha
  • 8,486
  • 8
  • 48
  • 70
1
vote
2 answers

ORACLE Mutating Table error in one trigger, but not another; Why?

Okay, I have two tables - ORDERS and ORDERLINES - which have essentially the same problem, with triggers on each to address the issue. The issue is that in addition to the PK with table-level uniqueness, on a field called RECID, there is another…
eidylon
  • 7,068
  • 20
  • 75
  • 118
1
vote
1 answer

How to avoid ORA-04091 error within a trigger

I have an after update trigger (Trigger A) on table A which can make changes to table B. I also have an after update trigger (Trigger B) on table B, which makes no changes, but queries table A to some sanity checking on a denormalization. So Trigger…
Tom Tresansky
  • 19,364
  • 17
  • 93
  • 129
0
votes
3 answers

Clashing triggers - mutating table

I have a problem with a mutating table in Oracle. I have two tables, Customer and Person. I must update ChangeDate in Customer during modification of a Person row, so I created a trigger. Unfortunately there is a trigger on Customer which updates…
Sławomir Rosiek
  • 4,038
  • 3
  • 25
  • 43
0
votes
2 answers

oracle sql developer table is mutating

i am trying to get a trigger to display an entire table called records_t upon a deletion, insertion or update. so i wrote this create or replace trigger display_rec after insert or update or delete on records_t for each row declare cursor cur…
atneyo
  • 1
0
votes
1 answer

trigger to update in Oracle

I've tried in many ways to create a trigger to update a field after this field is updated on other register. Always fail. When I use FOR EACH ROW I get the mutating table error. And when I try to update directly, I don't know how to reference the…
albertoivo
  • 385
  • 1
  • 3
  • 13
0
votes
1 answer

How to update a column in same table using calculated value in oracle trigger

I am creating a trigger in oracle where I need to update a column "subtotal" whenever there is an update in column "quantity" of the same table. so my update command which should fire this trigger is : Update TABLENAME set QUANTITY = 6 where…
pun_intended
  • 1
  • 3
1
2
3 4 5