| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Mutating tables?
A copy of this was sent to <yliu_at_creighton.edu>
(if that email address didn't require changing)
On Wed, 24 Mar 1999 13:18:32 -0600, you wrote:
>Hi All,
>
>I was wondering if someone could explain to me what the concept of
>mutating tables is. I looked up mutating tables in several of my manuals,
>but the concept is rather vague to me. If possible, could you please give
>me an example? How do you avoid this situation?
>
>Thank you very much for your help. Please reply to my e-mail address
>below.
>
>Yongge
>yliu_at_creighton.edu
>
I'm going to recycle an old post here (unfortunately deja news didn't seem to have it). What follows is my answer to a similar question. I've reposted both the question and answer -- you might want to read the question at the bottom first to get the point.
This explains why the mutating table error is raised (for your own good) and how to avoid it....
Since the table is in fact mutating with multi-row inserts, different results could be obtained *from the same exact insert statement* depending on the order of the rows in the subquery (eg: the insert run in cost based mode might result in a different answer then the query run in rule based).
For example, lets say the table was empty. We run a query to insert the values
(1, 1, 11 ); (1, 2, 12 ); (1, 3, 13 );
If the subselect returned the rows in the above order, then the trigger would cause 2 rows to be inserted into the carr_change table.
If, on the other hand, the rows were returned from the subselect (remember, same query being executed, we are just rearranging the order the rows happened to be inserted -- something you have NO control over) in the following order:
(1, 3, 13 ); (1, 2, 12 ); (1, 1, 11 );
No rows would be inserted, different from above (but totally uncontrolable).
so, it will let you do a singleton insert and use a BEFORE INSERT/FOR EACH ROW trigger that reads the table (this is not ambigous, we don't see the effects of our insert, there are no race conditions like above where we get different results on different runs of the same statement). On the singleton insert, the table has not yet begun to 'mutate' during the firing of the before insert/for each row trigger (but it has for the after insert/for each row trigger -- even with the singleton insert).
So, how to work around this? Use an after trigger. For example, I rewrote your trigger as follows:
create or replace package member_pkg
as
type rArray is table of rowid index by binary_integer;
rids rArray;
cnt number;
end;
/
create or replace trigger member_bi
before insert on member
begin
member_pkg.cnt := 0;
end;
/
create or replace trigger member_aifer
after insert on member
for each row
begin
member_pkg.cnt := member_pkg.cnt+1;
member_pkg.rids( member_pkg.cnt ) := :new.rowid;
end;
/
CREATE OR REPLACE TRIGGER member_ai
after INSERT ON member
DECLARE
l_prev_carr member.carr%TYPE;
l_rec member%rowtype;
begin
for i in 1 .. member_pkg.cnt loop
BEGIN
select * into l_rec from member where rowid = member_pkg.rids(i);
SELECT carr
INTO l_prev_carr
FROM member
WHERE group_id = l_rec.group_id
AND unit_id = (l_rec.unit_id - 1 );
IF l_rec.carr != l_prev_carr THEN
INSERT INTO carr_change (group_id, old_carr, new_carr)
VALUES (l_rec.group_id, l_prev_carr, l_rec.carr);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line( 'no data' );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
So that
select 1, 1, 11 from dual union all select 1, 2, 12 from dual union all select 1, 3, 13 from dual;
select * from carr_change;
rollback;
insert into member
select 1, 3, 13 from dual union all select 1, 2, 12 from dual union all select 1, 1, 11 from dual ;
select * from carr_change;
Now gives us the consistent (same) answer even though the rows are inserted 'backwards' in the 2 cases (if we didn't 'mutate' the table on you, the above 2 inserts would result in different answers using the single row trigger -- one would insert 2 rows, the other 0 rows into the carr_change table)
Since you only needed access to the :new values in the trigger, we were able to just save the rowids in the row trigger. If you needed old values, we would have had to of saved those values in their own arrays as well.
On Fri, 14 Nov 1997 09:32:35 -0500, Joe Rogers <joer_at_renewal-iis.com> wrote:
>I have a before insert trigger (row level) that works fine on INSERT
>INTO table VALUES(x, x, x), but if I do INSERT INTO table (SELECT x, x,
>x FROM other_table), it gives me "ORA-04091: table is mutating,
>trigger/function may not see it."
>
>What would cause this difference?
>
>Details:
>I have 2 tables:
>CREATE TABLE member
>(group_id NUMBER NOT NULL,
> unit_id NUMBER NOT NULL,
> carr NUMBER NOT NULL,
> CONSTRAINT pk_member PRIMARY KEY (group_id, unit_id) );
>
>CREATE TABLE carr_change
>(group_id NUMBER NOT NULL,
> old_carr NUMBER NOT NULL,
> new_carr NUMBER NOT NULL);
>
>I need to create a trigger so that when a row is inserted into member,
>it checks the previous unit in the group. If is has a different carr
>that the one being inserted, then I need to insert a row into
>carr_change for nightly batch processing. Thus, if I have (1, 1, 7) in
>the member table and I insert (1, 2, 6) I need to write a row (1, 7, 6)
>into carr_change.
>
>Here is the trigger I wrote:
>
>CREATE OR REPLACE TRIGGER before_member_trig
> BEFORE INSERT ON member
> FOR EACH ROW
>
>DECLARE
> l_prev_carr member.carr%TYPE;
>
>BEGIN
> SELECT carr
> INTO l_prev_carr
> FROM member
> WHERE group_id = :new.group_id
> AND unit_id = (:new.unit_id - 1) );
>
> IF :new.carr != l_prev_carr
> THEN
> INSERT INTO carr_change (group_id, old_carr, new_carr)
> VALUES (:new.group_id, l_prev_carr, :new.carr);
> END IF;
>
>EXCEPTION
> WHEN NO_DATA_FOUND THEN
> NULL; -- this is fine, first unit for a group
>
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE(SQLERRM);
>
>END;
>/
>
>
>This trigger works fine on an insert with a VALUES clause, but it chokes
>on an insert with a subselect. Mutating table error ORA -04091. Any
>ideas why it would act differently, and how to fix?
>
>Please copy any responses directly to me. Thanks.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |