Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Mutating Trigger??
A copy of this was sent to "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk_at_Feist.Com>
(if that email address didn't require changing)
On Tue, 24 Nov 1998 22:24:27 -0600, you wrote:
>In my opinion, Oracle should have fixed this long ago. But they took the
>cheap, easy way out and just created an error message. The problem is that
>when you insert, update, or delete from the EMP table, the dbms doesn't know
>for sure if there is some other insert, update, or delete on EMP that is in
>an inconsistant state (not yet committed, and not yet rolled back), and
>whether or not that other one is going to be sucessful, so the sum(sal) from
>emp is ambigious at best, and inconsistant at worst, and Oracle doesn't want
>anyone to be able to sue them for an inconsistancy in the data values caused
>by their DBMS, so they created that error message.
>
I beg to differ with you that Oracle took the 'cheap easy way' out. Its a rather complex problem and they way you describe it is wrong in fact. The DBMS does know if another insert update or delete is taking place (thats the whole foundation for multi versioning and consistent reads in Oracle actually). The problem isn't other updaters of the data at all -- its YOUR update that causes the inconsistencies. Your update is the problem, not others.
I'm including part of another posting where we describe this problem and the reason for mutating tables:
......<included from another post>......
>Hi,
>
>I'm having troubles with a mutating trigger here ...
>
>Here's an example of what I'm trying to do ...
>
>CREATE TRIGGER my_table_b_d_r
>BEFORE DELETE ON my_table
>FOR EACH ROW
>--
>DECLARE
> v_my_parent VARCHAR2(20);
>--
>BEGIN
>--
> SELECT name_child INTO v_my_parent
> FROM my_table
> WHERE child_nr = :OLD.child_parent;
>--
> INSERT INTO my_table_his
> (sequence, name_child, name_parent, date)
> VALUES
> (:OLD.sequence, :OLD.name_child, v_my_parent, sysdate);
the way to do this is to defer reading the table MY_TABLE (which is being changed at this point) until the AFTER DELETE trigger. The code following this demonstrates this with the EMP table found in the scott schema.
This example also convienently points out WHY there exists a mutating table problem -- why it is BAD to read a table while its mutating. If you look at the after trigger below (which tries to read the EMP table) we see that the data we are trying to read MAY IN FACT NO LONGER EXIST (our delete statement might have deleted the rows we wanted to read as well as the row that fired the trigger). In my example, we are using EMPNOS as a primary key and MGR as a foriegn key. We are trying to save the ENAME of the deleted row as well as the ENAME of the mgr pointed to by this row. What happens tho if we delete both the EMPLOYEE and their MGR in the same delete AND we let you read the table in the for each row trigger? If the table if mutating, depending on the rather arbitrary way we will delete records you may either get the employees manager record -- or not (we might delete the mgr before the emp or vice versa). In fact, if you put 2 records into the table (emp and mgr) and repeated the test over and over again the results would be indeterminate -- one time you might see the mgr record, the next time not.. it would not be deterministic -- leading to data inconsistencies and unreproducible sequences of events. This (below) makes it deterministic....
create table emp as select * from scott.emp;
alter table emp add constraint emp_pk primary key(empno);
alter table emp add constraint emp_fk foreign key(mgr) references emp(empno);
create table delete_history( ename varchar2(30), mgr_name varchar2(30), ts date);
create or replace package emp_pkg
as
type empnoArray is table of emp.empno%type index by binary_integer; type enameArray is table of emp.ename%type index by binary_integer;
empnos empnoArray;
empty empnoArray;
enames enameArray;
end;
/
create or replace trigger emp_bd
before delete on emp
begin
emp_pkg.empnos := emp_pkg.empty;
end;
/
create or replace trigger emp_adfer
after delete on emp for each row
begin
emp_pkg.empnos( emp_pkg.empnos.count+1 ) := :old.mgr;
emp_pkg.enames( emp_pkg.empnos.count ) := :old.ename;
end;
/
create or replace trigger emp_ad
after delete on emp
begin
for i in 1 .. emp_pkg.empnos.count loop
insert into delete_history select emp_pkg.enames(i), ENAME, sysdate from emp where empno = emp_pkg.empnos(i); if ( sql%rowcount = 0 ) then -- you deleted >1 record and got this guys MGR as well insert into delete_history values ( emp_pkg.enames(i), NULL, sysdate ); end if;
the problem, as described above, is that a simple change in the ACCESS method of an update for example will give you DIFFERENT results if you are allowed to read a table as it is being changed. Sometimes you will get what you want -- sometimes not. The table itself is inconsistent during your update (what if EMP has a foreign key to itself pointing from EMPNO -> MGR -- your trigger will see the IMPOSSIBLE sometimes {if it was allowed to that is}, that of an emp record with a MGR value that has no row in emp since we deleted the emp row for that mgr first)... Its not reproducible and a bear to discover and debug and subsequently FIX.
The mutating table constraint is there to protect the data, I do agree 100% (even more perhaps) with you statment:
<quote>
>and Oracle doesn't want
>anyone to be able to sue them for an inconsistancy in the data values caused
>by their DBMS
</quote>
although 'suing' isn't the underlying reason. Oracle has this thing about providing correct, consistent data, its sort of our mantra -- our reason for being if you will.
I've also posted the answer to the original question which can be solved easily in a single, for each row trigger:
SQL> create or replace trigger MAINTAIN_DEPT_SALARIES
2 after INSERT OR UPDATE OR DELETE ON EMP
3 for each row
4 begin
5 if ( inserting OR updating ) then 6 update dept set tot_sal = tot_sal + :new.sal 7 where deptno = :new.deptno; 8 end if; 9 if ( deleting OR updating ) then 10 update dept set tot_sal = tot_sal - :old.sal 11 where deptno = :old.deptno; 12 end if;
[snip]
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Nov 25 1998 - 08:07:54 CST