Home » SQL & PL/SQL » SQL & PL/SQL » Read same table during Update Trigger Error
Read same table during Update Trigger Error [message #37076] Wed, 16 January 2002 09:20 Go to next message
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 Go to previous message
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.
Previous Topic: create file using utl_file
Next Topic: Active table-update monitoring (using pl/sql?)
Goto Forum:
  


Current Time: Mon Apr 06 01:25:23 CDT 2020