| Read same table during Update Trigger Error [message #37076] |
Wed, 16 January 2002 09:20  |
kriser
Messages: 11
Registered: October 2001
|
Junior Member |
|
|
All
My problem:
I am trying to read the same table that is being updated during a trigger to determine if the Update should be allowed.
Specs:
If a default indicator has been assigned to another row on the table I do not want the update to occur.
When I apply this logic to a BEFORE INSERT trigger it works fine,
but when I try to apply this logic to a BEFORE UPDATE trigger I get the following message:
ORA-04091 table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table
that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.
Does anyone know of a way doing this or a workaround?
|
|
|
|
| Re: Read same table during Update Trigger Error [message #37081 is a reply to message #37076] |
Wed, 16 January 2002 11:52  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
row level triggers raises this error. are you trying in row level trigger?.
work around is:
1) create package variable
create package pkg1 is
p number;
end;
2) create before update stmt level trigger and query table and store value in pkg1.p variable
create or replace trigger trig1
before update on tableA
begin
select b into pkg1.p from tableA where a=1;
end ;
3) create before update row level trigger and check value of pkg1.p
4) create after update trigger (stmt level) and set pkg1.p value to null.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
