Stack Overflow
Stack Overflow

Questions tagged [ora-04091]

table name is mutating, trigger/function may not see it

Cause:

A statement executed a trigger or custom PL/SQL function. That trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function.

Solution

Re-write the trigger/function so that it does not try to modify/query the table in question.

32 questions
6
votes
4 answers

ORA-04091: table [blah] is mutating, trigger/function may not see it

I recently started working on a large complex application, and I've just been assigned a bug due to this error: ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4 ORA-04088: error…
Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
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
2 answers

Oracle After Delete Trigger... How to avoid Mutating Table (ORA-04091)?

Let's say we have the following table structures: documents docmentStatusHistory status +---------+ +--------------------+ +----------+ | docId | | docStatusHistoryId | | statusId | +---------+ +--------------------+ …
Paulo Santos
  • 11,285
  • 4
  • 39
  • 65
4
votes
2 answers

mutating, trigger/function may not see it- error during execution of trigger

CREATE OR REPLACE TRIGGER UPDATE_TEST_280510 AFTER insert on TEST_TRNCOMPVISIT declare V_TRNCOMPNO NUMBER(10); CURSOR C1 IS SELECT B.COMPNO FROM TEST_TRNCOMPVISIT A, TEST_TRNCOMPMST B, TEST_MEMMAST C WHERE…
mahesh soni
  • 43
  • 1
  • 3
3
votes
2 answers

Oracle Compound trigger - how to store and "use" deleted rows? INDEX BY table?

I am fighting a long struggle now for a DELETE trigger in Oracle, which, upon deleting a line, selects a new MAX value from the remaining rows and writes it to another table. After stumbling across the annoying ORA-04091 mutating table error (can't…
Erik Hart
  • 1,114
  • 1
  • 13
  • 28
2
votes
3 answers

Oracle calculate average using a trigger

For a school project we are forced to have redundant information and update it by using triggers. We have a table called 'recipe_ratings' that contains a 'rating' (numbers 0-100). In our 'recipes' table we have a redundant row called 'rating' that…
Jens
  • 1,499
  • 3
  • 17
  • 28
2
votes
1 answer

Delete rows matching substring with LIKE?

How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess). My first attempt was: delete from longtable where…
Sam
  • 581
  • 4
  • 17
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
0 answers

I was trying to write a trigger but it gives Error like this in PL/SQL

My code is: CREATE TABLE ACCOUNT(Account_no Varchar2(5), Account_Name Varchar2(5), Account_Balance number); INSERT INTO ACCOUNT VALUES('A1','C1',5000); INSERT INTO ACCOUNT VALUES('A2','C2',15000); CREATE TABLE TRANSACTION(Transaction_no number,…
Moonwar
  • 31
  • 1
  • 4
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

How to use data from the updated table in a trigger?

So I have these two tables: create table CURRENCY ( name VARCHAR2(40 CHAR) PRIMARY KEY, value NUMBER(6,2) ); create table EXCHANGE_RATE ( currency1 references CURRENCY(name), currency2 references CURRENCY(name), price…
bebewr
  • 96
  • 6
1
vote
3 answers

ORA-04091 - How can I alter a table that a trigger fires on?

So I have table foo and I would like to delete other foo rows when trigger t_foo fires: CREATE OR REPLACE TRIGGER "t_foo" AFTER INSERT OR DELETE OR UPDATE ON foo /*delete some other records from foo that are not :NEW.* or :OLD.* \* How would I go…
Shawn
  • 7,235
  • 6
  • 33
  • 45
1
vote
1 answer

Trigger Compiles Fine But Encounters ORA-04091 on UPDATE

1.) I coded a trigger for a table called grades as follows: CREATE OR REPLACE TRIGGER grades_before_update BEFORE UPDATE OF grade_percent ON grades FOR EACH ROW DECLARE grade_percent_var NUMBER; BEGIN SELECT grade_percent INTO …
5120bee
  • 689
  • 1
  • 14
  • 36
1
vote
0 answers

Trigger can not works - ora-04091 tabla is muting

Hy, I'm beginner in oracle :) I would like to write a trigger what is watch some record in my AAAA table and if somebody modify some record trigger going to insert into another (BBBB) table the date of modification and modified value. CREATE OR…
A.Istvan88
  • 11
  • 1
1
vote
4 answers

problem with trigger in oracle

the problem is this : I implemented a trigger on the table called CLAN_AFFILIATI that increases (if inseriemento) and decreases (in case of cancellation) an attribute (NUMAFFILIATI) of another table called CLAN. what I would do is block the update…
kafka
  • 949
  • 12
  • 27
1
2 3