|
|
|
|
|
|
| Re: Mutating error(need clarification) [message #135741 is a reply to message #135709] |
Fri, 02 September 2005 06:10   |
sharwan
Messages: 6
Registered: August 2005
Location: delhi
|
Junior Member |
|
|
hai,
mutating triggered occur when we make a dml trigger on a specefic
condition just like
we make a dml trigger on employees table THAT PREVENT SAL BETWEEN 2000 AND 6000
THEN U R NOT ABLE TO UPDATE SAL TO ANY EMPLOYEE BET.. 2000 AND 6000
2>IN SECOND CONDITION REGARDING FOREIGN KEY
CREATE OR REPLACE TRIGGER UPD_PLAYER_STAT_TRIG
AFTER INSERT ON PLAYER
FOR EACH ROW
BEGIN
INSERT INTO PLAYER_BAT_STAT(PLAYER_ID,
SEASON_YEAR,AT_BATS,HITS)
VALUES(player_id_seq.currval, 1997, 0, 0 );
END;
After creating this trigger, you test it by inserting a row into the PAYER table. You
receive this error message:
ORA-04091: table SCOTT.PLAYER is mutating,trigger/function may not see it.
How can you avoid getting this error?
A. Drop the foreign key contraint on the PLAYER_ID column of the
PLAYER_BAT_STAT table.
B. Drop the primary key contraint on the PLAYER_ID column of the
PLAYER_BAT_STAT table.
C. Drop the primary key constraint on the ID column of the PLAYER table.
D. The code of the trigger is invalid. Drop and recreate the trigger.
Answer: A
|
|
|
|
| Re: Mutating error(need clarification) [message #137603 is a reply to message #135709] |
Thu, 15 September 2005 11:40 |
azeem87
Messages: 116
Registered: September 2005
Location: dallas
|
Senior Member |
|
|
Hi
Mutating is a situation where you have a trigger and u r performing some DML operation on a table and from that same trigger u r trying to perform a select operation ,at this time u get mutating error.
U can describe it as Your select statement is not in a position to get the actual state of data since there is DML being performed on that table.
Hope it is clear and if i am wrong please correct it.
Thanks
|
|
|
|
| Re: Mutating error(need clarification) [message #533410 is a reply to message #137603] |
Tue, 29 November 2011 22:40 |
satheessh
Messages: 5
Registered: November 2011
Location: Hyderabad
|
Junior Member |
|
|
Mutating Error:-When a table is in half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger some where else in the same time then at that instance mutating trigger (or) mutating error occurs.
(OR)
If the trigger attempts to select or modify the table while the trigger has not completed (i.e: table is in transaction) then mutating trigger occurs.
This Trigger can be avoid by using PRAGMA AUTONOMOUS_TRANSACTION
[Updated on: Tue, 29 November 2011 22:47] Report message to a moderator |
|
|
|
| Re: Mutating error(need clarification) [message #533446 is a reply to message #533410] |
Wed, 30 November 2011 00:54 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:This Trigger can be avoid by using PRAGMA AUTONOMOUS_TRANSACTION
This is WRONG WRONG WRONG.
NEVER use this pragma to workaround this error.
This error means your code and design is wrong.
Do you cut a arm when you burn a finger?
Regards
Michel
[Updated on: Wed, 30 November 2011 00:55] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Mutating error(need clarification) [message #533518 is a reply to message #533514] |
Wed, 30 November 2011 06:23 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The answer is:
* Analyze the requirements that lead you to write this code.
Most of the time the requirements are not good.
For instance, the most often seen, the requirement says you must have an aggregate field on the row (for instance the number of employees with the same job in your department).
This requirement is not correct. This information must not be stored in a table column but must be calculated on the fly maybe interfacing with the application using a view, or, in a DWH where there is no DML, with a materialized view.
There are special cases where you need set consistency. For instance, there can be only 1 president in employees table. This case can be implemented using a complex mechanism of triggers and tables but can't be done in a single one.
The most important problem is not to make it work on your PC where you are alone, but to make it work when there are multiple sessions accessing your table.
Assertions (such as in the previous example) can currently not be natively addressed by Oracle (or any rdbms). Maybe in a future version...
For now, you have to use a product such as RuleGen.
Regards
Michel
|
|
|
|
| Re: Mutating error(need clarification) [message #533519 is a reply to message #533514] |
Wed, 30 November 2011 06:24 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
satheessh wrote on Wed, 30 November 2011 11:53Thank you very much Michel......... If the Question raised how to avoid Mutating Trigger. What answer should i give..????
The error is mutating table not trigger.
You avoid it by redesigning your DB/application so that you don't need to write code in a trigger that would cause that error.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
