Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Table Error
Mutating Table Error [message #589301] Thu, 04 July 2013 03:15 Go to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
Hello,

I'm fairly new to PL/SQL and came across an error I can't seem to find a solution for.

Following situation:

Table A basically has 4 rows of interest, an outside event changes/inserts let's say row 1.
Row 2 and 3 have to be changed in a trigger depending on the new value of 1.(or not if the value stays the same)

Row 4 is the reference for the update and will not be changed.

My first simple AFTER INSERT or UPDATE trigger obviously failed because of the mutating table error.

I've read a lot of similar posts and solutions but I can't get a solution which would work on this particular situation.

Thanks a lot in advance!

Best regards,

Martin
Re: Mutating Table Error [message #589303 is a reply to message #589301] Thu, 04 July 2013 03:23 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A Mutating table error usually points to a wrong database design. In your case, what happens when someone changes row 1 while somebody else updates row 2?

The best solution to a mutating table is to fix the design.
Re: Mutating Table Error [message #589304 is a reply to message #589301] Thu, 04 July 2013 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you have a table where updates fire a trigger that in turn updates the same table?
That says there's something seriously wrong with your data model.

Think about what would happen if you didn't get mutating table:
update would fire the trigger, which would do update, which would fire trigger, which would do update.
Repeat.

The best solution will be to change your table structure to avoid having to do any of this in the first place.

Other solutions may be possible, but we can't help there unless you give us a lot more information about what you are trying to do - a Test case in other words - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.
Re: Mutating Table Error [message #589305 is a reply to message #589303] Thu, 04 July 2013 03:28 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
ThomasG wrote on Thu, 04 July 2013 03:23
A Mutating table error usually points to a wrong database design. In your case, what happens when someone changes row 1 while somebody else updates row 2?

The best solution to a mutating table is to fix the design.

I know that this usually points to a design mistake, but I have no influence on design changes,I'm just a student working during the summer Wink

Tbh I don't know what would happen , but I think row 2/3 aren't meant to be updated by a user anyway.

Re: Mutating Table Error [message #589306 is a reply to message #589305] Thu, 04 July 2013 03:41 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. The *second* best solution would be to change the way the update happens, so that the complex logic happens in the calling application, not in a trigger.

The *third* best option is to create a view on the table, have the update/insert happen on the view and do the table manipulation in an "instead of" trigger.
Re: Mutating Table Error [message #589309 is a reply to message #589304] Thu, 04 July 2013 04:20 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
cookiemonster wrote on Thu, 04 July 2013 03:26
So you have a table where updates fire a trigger that in turn updates the same table?
That says there's something seriously wrong with your data model.

Think about what would happen if you didn't get mutating table:
update would fire the trigger, which would do update, which would fire trigger, which would do update.
Repeat.

The best solution will be to change your table structure to avoid having to do any of this in the first place.

Other solutions may be possible, but we can't help there unless you give us a lot more information about what you are trying to do - a Test case in other words - create table statements and insert statements for a small, representative sample of data, then we'll be able to work with your tables and data.

I thought about that too, that problem shouldn't occur with
AFTER UPDATE or INSERT 
OF row 1 ON Table A

,right?

Ok, will try to get some data Smile

ThomasG wrote on Thu, 04 July 2013 03:41
Well. The *second* best solution would be to change the way the update happens, so that the complex logic happens in the calling application, not in a trigger.

The *third* best option is to create a view on the table, have the update/insert happen on the view and do the table manipulation in an "instead of" trigger.


So there is no way to solve this problem just with triggers ? I read about compound triggers and their potential to solve mutation errors, that's why I'm asking.



edit:

I tried to get some sample data , I can't test those statements so I hope they are correct:
CREATE TABLE 'TABLE A'
(Id NOT NULL,
row1 int,
row2 int,
row3 varchar[20],
row4 int,
row5 int);

INSERT INTO 'TABLE A' (row1, row2, row3,row4,row5)
VALUES (8517610090,1,NAR,1,1197865744);

INSERT INTO 'TABLE A' (row1, row2, row3,row4,row5)
VALUES (8517610090,1,NAR,1,1197865718;

INSERT INTO 'TABLE A' (row1, row2, row3,row4,row5)
VALUES (01022929,1,NAR,1,5500782004);

INSERT INTO 'TABLE A' (row1, row2, row3,row4,row5)
VALUES (01022910,1,NAR,1,5500782004);

row1 is, like before, the row which will be updated from a different programm/user
row2 is the first row which should be updated by the trigger and gets the data from row4(TBH I don't know why)
row3 is the second row which should be updated , currently I can set "NAR" for testing purposes, don't really know where the data will come from usually(not from this table tho)
row5 is the reference row but not a primary key as it can have double entrys.

[Updated on: Thu, 04 July 2013 04:59]

Report message to a moderator

Re: Mutating Table Error [message #589310 is a reply to message #589309] Thu, 04 July 2013 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A compound trigger might get rid of the mutating table error message.

But the problem that updates from two concurrent sessions might create inconsistent data still exists. So whether a compound trigger works as expected depends on the exact data model and the exact statements that are passed to the database.

You would basically need a "before statement" part that locks all relevant rows, a "before/after row" part that remembers the changed values in another data structure, and a "after statement" part that applies the additional updates to the table and unlocks the affected rows. Which requires a quite intricate knowledge of both the data model and all possible client statement that might arrive at the database.

I remember one instance where a compound trigger worked quite well during 99% of the time, but completely broke a year-end processing batch in a way that was only discovered days later after the wrong data had already been passed on and caused quite some disturbance.
Re: Mutating Table Error [message #589315 is a reply to message #589310] Thu, 04 July 2013 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
maisi wrote on Thu, 04 July 2013 10:20

CREATE TABLE 'TABLE A'
(Id NOT NULL,
row1 int,
row2 int,
row3 varchar[20],
row4 int,
row5 int);


Table name shouldn't be in quotes. id is missing a datatype.
Datatype sizes need to be in normal brackets not square ones.

Your inserts shouldn't have quotes round the table name either and the skip the not null column.

If you don't have a DB you can test these things on I suggest you install oracle XE.

maisi wrote on Thu, 04 July 2013 10:20

row1 is, like before, the row which will be updated from a different programm/user

The row or the column?

maisi wrote on Thu, 04 July 2013 10:20

row2 is the first row which should be updated by the trigger and gets the data from row4(TBH I don't know why)

Again, row or column?

maisi wrote on Thu, 04 July 2013 10:20

row3 is the second row which should be updated , currently I can set "NAR" for testing purposes, don't really know where the data will come from usually(not from this table tho)

Again, row or column?

maisi wrote on Thu, 04 July 2013 10:20

row5 is the reference row but not a primary key as it can have double entrys.

I have no idea what that means.
Re: Mutating Table Error [message #589318 is a reply to message #589315] Thu, 04 July 2013 06:31 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
@cookiemonster:

I'm sorry for those mistakes! Sad I started installing oracle XE around an hour ago and just tried to edit the statements :/

Oh my god this is embarrassing, I mixed up row and column , ofc I'm talking about columns , really sorry about that !

The entries in column5 reference a shipmentnumber (which are saved in a different table), since there can be more than one item per shipment this shipmentnumber can occur on different table entries.

ID  Column1   Column2  Column3  Column4  Column5
1  1022910	1	NAR	1	5500782004
2  8517610090	1	NAR	1	1197865744
3  1022929	1	NAR	1	5500782004
4  8517610090	1	NAR	1	1197865718



@ThomasG:

Ok thanks for your input , I will try to write such a compound trigger and report back.

Hmm 99% might not be good enough, we already have a ton of support to do because of misbehaving statements, but I will at least try that opportunity.


[EDITED by LF: removed quotes of whole previous messages]

[Updated on: Thu, 04 July 2013 07:33] by Moderator

Report message to a moderator

Re: Mutating Table Error [message #589320 is a reply to message #589318] Thu, 04 July 2013 06:47 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
maisi wrote on Thu, 04 July 2013 13:31
Oh my god this is embarrassing, I mixed up row and column , ofc I'm talking about columns , really sorry about that !


Ah, that might make it definitely simpler. If you don't have to really access "other rows" of the table where the trigger is on, then you should be able to do any manipulations of column data with via :new.columnX and :old.columnX in the trigger, and there never should be any mutating table issues.


[EDITED by LF: fixed [quote] tags]

[Updated on: Thu, 04 July 2013 07:34] by Moderator

Report message to a moderator

Re: Mutating Table Error [message #589321 is a reply to message #589320] Thu, 04 July 2013 06:54 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well I'm still none the wiser is to what problem you're actually trying to solve but, as Thomas says, it looks like mutating table is irrelevant and you shouldn't need multiple triggers or a compound trigger.
Of course that begs the question of how you managed to hit a mutating table error in the first place.
Re: Mutating Table Error [message #589323 is a reply to message #589321] Thu, 04 July 2013 07:16 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
Well this is the trigger that was given me to work with , which is mutating :

create or replace 
Trigger TRG_TEST_2
AFTER UPDATE or INSERT
on A REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

updtstmt varchar2(1000);

Begin

   
    updtstmt:='update A set Column3 = ''NAR'', Column2 ='||:NEW.Column4||' where Column5 = '''||:NEW.Column5||'''';
     dbms_output.put_line('updatestatement: '||updtstmt);
     
     
     execute immediate updtstmt;
    

END;


I basically tried to get it to run.
Re: Mutating Table Error [message #589324 is a reply to message #589323] Thu, 04 July 2013 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gee! There are so many errors in so few lines.

Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel

[Updated on: Thu, 04 July 2013 07:18]

Report message to a moderator

Re: Mutating Table Error [message #589326 is a reply to message #589324] Thu, 04 July 2013 07:31 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
And that does indeed update other rows in the same table. And if you didn't get a mutating table error it would go into an infinite loop as I suggested in my first post.

So we're back to needing you to accurately describe the logic you're trying to apply.

It also has completely unnecessary dynamic sql.
Re: Mutating Table Error [message #589337 is a reply to message #589326] Thu, 04 July 2013 08:12 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
Assuming it doesn't have to update other rows (which is the case as far as I know), how would you write the trigger ?

Thanks @All for your help and time !


[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Thu, 04 July 2013 15:29] by Moderator

Report message to a moderator

Re: Mutating Table Error [message #589338 is a reply to message #589337] Thu, 04 July 2013 08:16 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
:new.<column> := <value>


Read the link Michel posted.
Re: Mutating Table Error [message #589374 is a reply to message #589338] Thu, 04 July 2013 17:57 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
maisi wrote on Thu, 04 July 2013 13:16
Well this is the trigger that was given me to work with , which is mutating :

create or replace 
Trigger TRG_TEST_2
AFTER UPDATE or INSERT
on A REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

updtstmt varchar2(1000);

Begin

   
    updtstmt:='update A set Column3 = ''NAR'', Column2 ='||:NEW.Column4||' where Column5 = '''||:NEW.Column5||'''';
     dbms_output.put_line('updatestatement: '||updtstmt);
     
     
     execute immediate updtstmt;
    

END;


I basically tried to get it to run.


Okay I can tell you now (as I'm studying for the PL/SQL exam and this is a topic that comes up) that

FOR EACH ROW

is often the cause of mutating table errors. You are NOT allowed to query or modify INSIDE the trigger the SAME table on which the triggering statement is based if it's a row level trigger (as identified by "FOR EACH ROW").
E.g. if you have a DELETE (FOR EACH ROW) trigger on EMP; you CANNOT in the DELETE trigger query EMP (e.g. to find out how many rows now exist in the table i.e. post-deletion). However, you CAN query or modify the table in an AFTER STATEMENT trigger. But you can't do it in a FOR EACH ROW trigger, because the table is changing while you query it. If you declare your trigger as AUTONOMOUS (dodgy) then you CAN apparently QUERY the table in a FOR EACH ROW trigger but you still cannot update it.

Without looking at your particular circumstances in general the advice in such situations is to do one of the following:
a) remove FOR EACH ROW, i.e. turn it into an AFTER STATEMENT level trigger
b) use a compound trigger (but you would still be using a statement-level trigger to do stuff after the initial triggering DML)

If needed you can write to package variables inside your FOR EACH ROW trigger and then reference those variables to do stuff in your AFTER STATEMENT trigger.
If you have a compound trigger then all the timing points of the compound trigger share a common set of variables set up in the declaration block of the compound trigger. There is an example at http://www.oracle-base.com/articles/11g/trigger-enhancements-11gr1.php but there must be many such examples on the Interweb.

So you have options.

Hope that helps.

[Updated on: Thu, 04 July 2013 18:01]

Report message to a moderator

Re: Mutating Table Error [message #589386 is a reply to message #589338] Fri, 05 July 2013 02:47 Go to previous messageGo to next message
maisi
Messages: 7
Registered: July 2013
Location: Austria/Vienna
Junior Member
cookiemonster wrote on Thu, 04 July 2013 15:16
:new.<column> := <value>


Read the link Michel posted.

Works as expected ,now using a BEFORE trigger and setting the value with :new.. Smile

Thanks a lot(@ALL)for your help/time/effort and sorry for the confusion and noob mistakes Embarassed

[Updated on: Fri, 05 July 2013 05:24]

Report message to a moderator

Re: Mutating Table Error [message #589388 is a reply to message #589374] Fri, 05 July 2013 03:05 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
mikomi wrote on Thu, 04 July 2013 23:57
If you declare your trigger as AUTONOMOUS (dodgy) then you CAN apparently QUERY the table in a FOR EACH ROW trigger but you still cannot update it.

So dodgy that it shouldn't be used - the queries will not see any changes made by the current main transaction. If you find yourself using autonomous_transaction in a trigger you are doing something wrong.

autonomous_transaction has one practical use - to record logging information in a table even if the parent transaction is rolled back.
Re: Mutating Table Error [message #589389 is a reply to message #589386] Fri, 05 July 2013 03:06 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
maisi wrote on Fri, 05 July 2013 08:47
Works as expected ,now using a BEFORE trigger and setting the value with :new.. Smile



thanks for letting us know.
Previous Topic: Exception error
Next Topic: Counting followed last ocurrences
Goto Forum:
  


Current Time: Tue Sep 02 05:23:05 CDT 2014

Total time taken to generate the page: 0.11945 seconds