Table Mutating

TLiberty · Jan 3, 2000

I'm learning oracle and have a problem understanding the message "Table: Client mutating... can not see trigger or function..." 
 
Before i state my question in detail (i know you guys already understand the problem from the message above) 
 
Entities of client 
Client_number (primary key) 
Tech_number 
FName 
LName 
 
Entities of UpdateLog 
UpdateID (sequence, primary key) 
Comments 
Client_Number (foreign key) 
DateUpdated 
UpdatedBY (default log on user) 
 
Packages Procedure 
InsertIntoUpdateLog( *parameters ) 
*Same as UpdateLog entities 
 
In trigger i have 
 
PKG_NAME.INSERTINTOUPDATELOG( UPDATEID.NEXTVAL,strCOMMENTS, SYSDATE, USER); 
 
When i insert a record in client it always give the error stated above. What am I doing wrong? Please help.

MikeLacey · Jan 4, 2000

Do you get an Oracle Error Message with a number? 
 
-ml 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

yaffle · Jan 5, 2000

The mutating table error occurs when you try and perform a DML operation on the same table on which a row level trigger is firing. 
I suspect that you have a trigger being fired for each row that is inserted into your client table, and that your packaged procedure is trying to do a select on client information. 
It may be that you could pass in the information needed by the procedure - i.e. using :NEW.column_name_of_client_info_that_proc_needs to refer to the required value in the newly inserted row. 
Have a look for any DML that the procedure may be doing involving the client table; if you're still stuck, if you could give the heading of the trigger definition that would help.

TLiberty · Jan 5, 2000

yaffle, 
 
This is the trigger attached to client and below is the package body: 
 
I'm not near my pc right now, but the trigger is similar to this: 
 
CREATE TRIGGER TAI_UPDATELOG 
AFTER INSERT ON CLIENT 
FOR EACH ROW 
REFERENCING OLD AS OLD, NEW AS NEW 
BEGIN 
-- ALSO, I DIDN'T MENTION I HAVE ANOTHER FIELD 
-- IN CLIENTS CALLED TECH_NUMBER 
 
PKG_UPDATELOG_ROUTINES.INSERTINTOUPDATELOG(SEQUENCE_UPDATEID.NEXTVAL, :NEW.CLIENT_NUMBER, SYSDATE, USER, :NEW.TECH_NUMBER, 'JUST A TEST INFORMATION'); 
 
END TAI_UPDATELOG; 
 
 
Package body, 
CREATE OR REPLACE PACKAGE BODY PKG_UPDATELOG_ROUTINES AS 
... -- OTHER ROUTINES NOT INTERESTED IN THOSE 
 
PROCEDURE INSERTINTOUPDATELOG(UPDID IN NUMBER, CLIENTNO IN NUMBER, DATEIN IN DATE, USERIN IN VARCHAR2, TECHNO IN NUMBER, COMMENTSIN IN VARCHAR2) AS 
BEGIN 
INSERT INTO UPDATE_LOG VALUES (UPID, CLIENTNO, DATEIN, USERIN, TECHNO, COMMENTSIN); 
COMMIT; 
END INSERTINTOUPDATELOG; 
END PKG_UPDATELOG_ROUTINES; 
 
Thanks alot.

yaffle · Jan 6, 2000

I'm not sure that the commit statement in your procedure will work properly when invoked from a trigger; you could try taking that out and leaving the transaction processing around your client insert to handle it. 
Alternatively, do you perhaps have a further trigger on the update_log table ? 
Failing that, I think the full error text is probably needed.

MikeLacey · Jan 7, 2000

I don't think you can do this - look at your code - if the DBMS did as you tell it - it would go up its own a**s real quick. That's a recursive thing you've got there and Oracle just won't let you do it. I would guess that it you comment out the INSERT INTO UPDATE_LOG statement it runs just fine? 
 
Mike 
Mike Lacey <a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a> <a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a>

yaffle · Jan 7, 2000

I don't think there's any recursion going on here. There's nothing wrong with using a trigger to automatically insert a row into a table when one is added to a different table; if both table have triggers accessing each other perhaps. 
Having had a look at the code again another thing occurs to me - I'm not sure about the reference to SEQUENCE_UPDATEID.NEXTVAL as I think sequences are restricted to use in certain SQL statements. Try putting it in the values clause of the insert statement in the procedure, rather than passing it in.

TLiberty · Jan 7, 2000

The actual error messages are: 
 
ORA-04091: table LIBTECH.CLIENT is mutating, trigger/function may not see it. 
0RA-06512: at LIBTECH.PKG_PILOTSERVICES line 6 
ORA-06512: at LIBTECH.TAI_UPDATELOG line 2 
ORA-04088: error during execution of trigger 'LIBTECH.TAI_UPDATELOG'. 
 
Sample code again from my laptop (I have Oracle Navigator): 
Trigger 
CREATE OR REPLACE TRIGGER TAI_UPDATELOG 
AFTER INSERT ON CLIENT 
REFERENCING OLD AS OLD NEW AS NEW 
BEGIN 
PKG_PILOTSERVICES.INSERTINTOUPDATELOG

NEW.TECH_NUMBER, :NEW.CLIENT_NUMBER, USER, 'JUST A TEST'); 
END; 
 
PACKAGE BODY PKG_PILOTSERVICES IS -- body (hidden part) 
-- private type and object declarations 
-- subprogram bodies 
PROCEDURE INSERTINTOUPDATELOG(TechNo IN VARCHAR2, ClientNo IN NUMBER, UpdateBy IN VARCHAR2 DEFAULT USER, CommentsIn IN VARCHAR2) IS 
BEGIN 
INSERT INTO UPDATELOG VALUES (UPDATEID.NEXTVAL, TechNo, ClientNo, SYSDATE, UpdateBy, CommentsIn); 
END INSERTINTOUPDATELOG; 
END; 
 
I moved the commit like you told me to, but no avail.

yaffle · Jan 10, 2000

Curiouser and curiouser... I haven't actually got access to Oracle at the moment on my current project, which make things a bit tricky to check, but: 
I notice the FOR EACH ROW is missing from your code now - I'd expect this to give a compile error, rather than runtime, but would make the :NEW reference invalid in any case. 
Is it possible that there are any other triggers around firing on CLIENT or UPDATELOG ? Check the all_triggers system view. 
You could try dropping and recompiling the trigger and package too - to ensure your latest versions are being run.

yaffle · Jan 11, 2000

Not able to get to Oracle, can get to the technet web page ;-) 
 
ORA-04091: table name is mutating, trigger/function may not see it 
 
Cause: A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger. 
 
Action: Rewrite the trigger or function so it does not read the table. 
 
 
Valid SQL Statements in Trigger Bodies: 
The body of a trigger can contain DML SQL statements. It can also contain SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor). 
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. The commands ROLLBACK, COMMIT, and SAVEPOINT cannot be used. 
Note: A procedure called by a trigger cannot execute the above transaction control statements because the procedure executes within the context of the trigger body. 
 
 
Mutating and Constraining Tables: 
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress. 
Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. 
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data. 
The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. 
The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement. There is an exception to this restriction; BEFORE ROW and AFTER ROW triggers fired by a single row INSERT to a table do not treat that table as mutating or constraining. Note that INSERT statements that may involve more than one row, such as INSERT INTO emp SELECT . . ., are not considered single row inserts, even if they only result in one row being inserted. 
 
If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers-an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table. 
 
 
My theory is that the foreign key on the UPDATELOG table makes it a constraining table for the CLIENT insert, so to work around it you'd need a temporary table that your client trigger inserts to instead of calling your procedure, with a statement level after insert trigger on it that called your procedure. 
Perhaps better would be to change your trigger to be a statement level trigger with a cursor of the form: 
select * from client c 
where not exists 
(select null from updatelog u 
where u.client_number = c.client_number); 
For each row returned by the cursor you could then call your procedure.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Table Mutating

TLiberty

Programmer

MikeLacey

MIS

yaffle

MIS

TLiberty

Programmer

yaffle

MIS

MikeLacey

MIS

yaffle

MIS

TLiberty

Programmer

yaffle

MIS

yaffle

MIS

Similar threads

Part and Inventory Search

Sponsor