| mutating table? [message #10550] |
Thu, 29 January 2004 21:07  |
Jeffrey
Messages: 30
Registered: January 2003
|
Member |
|
|
Let say i have 2 table 'A' and 'B'.
Table 'A' structure is ID,R_Number, and Result.
Table 'B' structure is Old_Num (value defaults to 0).
I have tried to write a trigger in 'A' that will fire once a new record is inserted or updated. The trigger will copy the value of R_Number from 'A' and insert/update table 'B'. Then another trigger will fire from 'B' once a record is updated/inserted. This trigger will call up a function to calculate some things.
Here is a desc of the trigger in 'A':
CREATE OR REPLACE TRIGGER "TR_A" AFTER
INSERT OR UPDATE OF "R_NUMBER" ON "A" FOR EACH ROW BEGIN
UPDATE B SET OLD_NUM=:new.R_NUMBER
WHERE ROWNUM= 1;
END;
/
Here is a desc of the trigger in 'B':
CREATE OR REPLACE TRIGGER "TR_B" AFTER
INSERT OR UPDATE OF OLD_NUM ON "B" FOR EACH ROW
BEGIN
UPDATE A SET RESULT=FunCalculate(:new.OLD_NUM) WHERE R_NUMBER=:new.OLD_NUM;
END;
/
This will result in error:-
Oracle Error:ORA-04091:... table is mutating, trigger/function may not see it
... (few more errors)
... .
... .
... .
Where suppose wrong here?
|
|
|
|
| Re: mutating table? [message #10557 is a reply to message #10550] |
Fri, 30 January 2004 02:03  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have an endless loop where the tables are continually mutating (changing). If you insert or update A, then a trigger fires that updates B, which fires a trigger that updates A, which fires a trigger that updates B and so on and so forth. There may also be problems associated with your FunCalculate function, but I can't tell since you did not include that code.
Please click on the link below for an article on the subject, by Tom Kyte.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
