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
6
votes
8 answers

Oracle triggers - problem with mutating tables

My tables: TableA (id number, state number) TableB (id number, tableAId number, state number) TableC (id number, tableBId number, state number) So items in TableC are TableB's children and items in TableB are TableA's children. Vice versa - items…
sventevit
  • 4,766
  • 10
  • 57
  • 89
5
votes
1 answer

How can i fix this Mutating table from a procedure an trigger

This trigger will pass on inserted values to a procedure which will insert those values in another table. I'm getting a mutating table error. How can i fix this? CREATE OR REPLACE TRIGGER ADD_INVOICE BEFORE INSERT OR UPDATE OF APP_NO,C_NO ON…
Shimmerstrike
  • 77
  • 6
3
votes
1 answer

ORACLE After update trigger: solving ORA-04091 mutating table error

I am trying to create a trigger: create or replace trigger NAME_OF_TRIGGER after insert or update on table1 REFERENCING OLD AS OLD NEW AS NEW for each row to fill in automatically a couple of non obligatory fields when updating/inserting on…
JoséNunoFerreira
  • 170
  • 1
  • 1
  • 10
2
votes
1 answer

Oracle Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502

I have a table trigger like below: CREATE OR REPLACE TRIGGER PAT_BUR_DOB_TRG BEFORE UPDATE OF DOB ON PAT REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW -- PL/SQL Block begin tgln_sys_error_pkg.compare_dates(trunc(add_months(:new.dob, -12)), …
user1308891
  • 23
  • 1
  • 1
  • 4
2
votes
3 answers

INSERT trigger for inserting record in same table

I have a trigger that is fire on inserting a new record in table in that i want to insert new record in the same table. My trigger is : create or replace trigger inst_table after insert on test_table referencing new as new old as old for each…
Yagnesh Agola
  • 4,556
  • 6
  • 37
  • 50
2
votes
2 answers

How to fire trigger after every insert - Oracle (Bulk insert)

I want the trigger to run after each and every record that gets inserted. This validation works fine if I have a simple insert like this: insert into g_dossier values (112334, 'BBT', 'LPO','TTS','Y') ; However, when it’s like a bulk…
Messady
  • 45
  • 2
  • 7
2
votes
1 answer

Table is mutating on BEFORE DELETE trigger

Basically, i have these 2 tables: CREATE TABLE Aposta ( codMovimento number(10) references Movimento(codMovimento), Primary key(codMovimento), data DATE default sysdate not null, valor Number(10,2) not null, quotaTotal…
lcvalves
  • 77
  • 1
  • 9
2
votes
2 answers

Solution to Oracle mutating trigger

I am stuck in a small requirement. my table should restrict if any overlapping data is getting inserted or updated. Below is my try so far: CREATE TABLE my_table ( ID NUMBER, startdate DATE, enddate DATE, CONSTRAINT…
ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41
2
votes
1 answer

Mutating trigger while selecting a table on delete trigger

Kindly help with the below. I have a table with two rows and while deleting one row I write a trigger and I want to write the records into a staging table(staging_tbl) of the other row which will be left after deletion. But it throws a mutating…
Shil N
  • 35
  • 1
  • 1
  • 7
1
vote
2 answers

Can statement-level triggers lead to mutating-tables

Or only row-level triggers are said to cause such mutations? I rephrase my question: Which trigger types will be impacted by constraining factors brought on by mutating tables?
Pop Stack
  • 926
  • 4
  • 19
  • 27
1
vote
2 answers

How to prevent a derived value (SUM) from being manually updated to an incorrect value

I am learning SQL and DB design for a college class. One assignment that was given to us is to create a table with a derived attribute which is the SUM of some child attributes. For example: ORDERS orderID {PK} /orderTotal /* derived from SUM…
The111
  • 5,757
  • 4
  • 39
  • 55
1
vote
1 answer

ORA-04091 tableT is mutating, trigger/function may not see it

create or replace trigger discount after insert or update on product for each row declare newcost number; quant number; id number; BEGIN id:= :NEW.pid; quant:= (30/100)*:NEW.req_quant; newcost:= :NEW.pcost - (10/100)*:NEW.pcost; if…
Abhishek Nikam
  • 11
  • 1
1
vote
1 answer

ORA-04091 table is Mutating

i have the following 2 table: runs: +--------+-------------+ | run_id | status | +========+=============+ | 1 | active | +--------+-------------+ | 2 | new | +--------+-------------+ and…
Bauerhof
  • 155
  • 11
1
vote
1 answer

Prevent delete from table if only one entry

I want to prevent deletion of a phone number if there is only one in the table at the time. So far I have this trigger: CREATE OR REPLACE TRIGGER T_TfnoCliente_Cliente BEFORE DELETE ON TFNO_CLIENTE FOR EACH ROW DECLARE --PRAGMA…
Marta Lobo
  • 175
  • 1
  • 16
1
vote
2 answers

SQL: trigger to prevent invalid data from being inserted into a table

I have the following table: CREATE TABLE booking( booking_id NUMBER(8) NOT NULL; booking_start DATE NOT NULL; booking_end DATE NOT NULL; booking_room NUMBER(3) NOT NULL; guest_no NUMBER(5) NOT NULL; ); This table keeps track of all bookings for…
Big Rick
  • 166
  • 9
1
2 3 4 5