| Mutating Table problem ... [message #41380] |
Tue, 24 December 2002 11:10  |
sai sreenivas jeedigunta
Messages: 370
Registered: November 2001
|
Senior Member |
|
|
Hello all ,
I have a table as follows
Sampletab
___________
X y
100 a
101 b
102 c
now i am writing a trigger as follows
create or replace trigger mytrig after insert
for each row
declare
Code varchar2(1);
begin
Select y into code from sampletab where x=:new.x;
myprocedure(code);
end;
where myprocedure is my procedure...
myprocedure will not insert/update/delete in sampletab table.
when i try to insert i am getting mutating trigger error...how to resolve this..
sai
|
|
|
|
| Re: Mutating Table problem ... [message #41382 is a reply to message #41380] |
Tue, 24 December 2002 21:23  |
 |
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your trigger code is incomplete. It doesn't say what table it is on. The "after insert" should be followed by "on some_table_name". It looks like your trigger is probably on the sampletab table and your myprocedure, that you didn't include the code for, also inserts into the sampletab table. This creates an endless loop, where an insert into the sampletab table causes the trigger to fire, which causes the myprocedure program to perform an insert on the sampletab table, which causes the trigger to fire, which causes the myprocedure program to perform an insert on the sampletab table, and so on, and so on.
Also, as a general rule, you cannot select from the same table, within a trigger, that the trigger is on, because the table is changing (mutating) at that point in time. You could change:
Select y into code from sampletab where x=:new.x;
to:
code := :NEW.y;
If you would post the code of myprocedure and a brief explanation of what you are trying to accomplish, perhaps we could offer an alternative. In the meanwhile, here is a link to some workarounds for similar problems:
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
