| URGENT: ORA-04091 table name is mutating, trigger/function may not see it [message #39203] |
Tue, 25 June 2002 22:48  |
Kjell
Messages: 7
Registered: February 2002
|
Junior Member |
|
|
Hi I'm getting the following error message
ORA-04091 table name is mutating, trigger/function may not see it
The cause is: 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.
Question: I want to read the table (in a procedure that is called by the trigger) How can I do this without getting an error??
All the best
Kjell-Ivar
|
|
|
|
|
|
| any suggestion how to alter my trigger to circumvent the problem of mutating table [message #39264 is a reply to message #39203] |
Mon, 01 July 2002 19:57  |
Rozario Mariadassou
Messages: 12
Registered: March 2002
|
Junior Member |
|
|
I want to build a generic procedure that can be used for maintianing a transaction log for audit / offline replication purposes. The procedure should be generic enough to be called by a trigger on any table . But I am running into the problem of mutating table. I am giving below the partial code for this generic procedure and the trigger on a sample table. Is there any other way I can achieve the same thing.
Best regards
CREATE PROCEDURE TEST
(IN_TABLE IN VARCHAR2,
IN_FLAG IN VARCHAR2,
IN_ROWID IN VARCHAR2) IS
SQLSTRING VARCHAR2(200);
V_TABLE VARCHAR2(100);
BEGIN
V_TABLE := 'TMP_' || IN_TABLE;
SQLSTRING := 'INSERT INTO ' || V_TABLE || ' (SELECT * FROM ' || IN_TABLE || ' WHERE ROWID = '''
|| IN_ROWID || ''')';
EXECUTE IMMEDIATE SQLSTRING;
END;
CREATE OR REPLACE TRIGGER TEST
AFTER INSERT OR DELETE OR UPDATE ON TMP FOR EACH ROW
DECLARE
V_FLAG VARCHAR2(1);
BEGIN
IF INSERTING THEN V_FLAG := 'I';
ELSIF UPDATING THEN V_FLAG := 'U';
ELSE V_FLAG := 'D';
END IF;
TEST('TMP',V_FLAG,:NEW.ROWID);
END ;
UPDATE TMP SET LOTNO='TESTING' WHERE LOTNO='401515';
ORA-04091: table MYL.TMP is mutating, trigger/function may not see it
ORA-06512: at "MYL.TEST", line 11
ORA-06512: at "MYL.TEST", line 8
ORA-04088: error during execution of trigger 'MYL.TEST'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
