Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER
Date: 1997/11/10
Message-ID: <3469263b.3854923_at_newshost>#1/1
[Quoted] On Sun, 09 Nov 1997 11:18:14 +0200, Isaac Chocron <itshak_at_hadassah.org.il> wrote:
>I intented to replace a phisical delete by a logical delete on my table
>T.
>I definided a Before delete trigger that performs:
>
>DECLARE
> NO_DELETE exception;
>Begin
> raise NO_DELETE;
>EXCEPTION
> when NO_DELETE then
> UPDATE T set HIDDEN_DATE = SYSDATE where rowid = :old.rowid;
>End;
>
>But I receive an ORA-04091: table T is mutating, trigger/function may
>not see it.
>
>A possible solution is to batch a database job, but I prefer a local
>solution to this error.
>
>Do you know another possible solution ?
Ok, I'll give you a 7.x solution and an 8.x solution (you don't mention the version you are using). For 8.x, the solution is pretty simple, it would look like this:
drop table delete_demo;
create table delete_demo ( a int primary key,
b date,
c varchar2(10),
hidden_date date );
create or replace view delete_demo_view as select a, b, c from delete_demo where hidden_date is null;
grant all on delete_demo_view to public;
create trigger delete_demo_o8
instead of delete on delete_demo_view
for each row
begin
update delete_demo set hidden_date = sysdate where a = :old.a;
end;
/
This shows off a new feature in o8, the instead of trigger. You can code triggers against views, letting you decide what to do for the action. In the above, we create a view that the world will use to insert/update/delete into the real table with. On this view, we create 'instead of' triggers when needed. Now we write the "instead of deleting do the update" logic you wanted.
insert into delete_demo_view values ( 1, sysdate, 'Hello' ); 1 row created.
select * from delete_demo_view;
A B C
---------- --------- ----------
1 10-NOV-97 Hello
delete from delete_demo_view;
1 row deleted.
select * from delete_demo_view;
no rows selected
select * from delete_demo;
A B C HIDDEN_DA ---------- --------- ---------- ---------
1 10-NOV-97 Hello 10-NOV-97
[Quoted] In version 7, we can't do this. The code to do the same thing in v7 is below:
drop table delete_demo;
create table delete_demo ( a int primary key,
b date,
c varchar2(10),
hidden_date date );
create or replace view delete_demo_view as select a, b, c from delete_demo where hidden_date is null; grant all on delete_demo_view to public;
[Quoted] create or replace package delete_demo_pkg as
type array is table of delete_demo%rowtype index by binary_integer;
oldvals array;
cnt number;
end;
/
create or replace trigger delete_demo_bd
before delete on delete_demo
begin
delete_demo_pkg.cnt := 0;
end;
/
create or replace trigger delete_demo_bdfer
before delete on delete_demo
for each row
declare
i number default delete_demo_pkg.cnt+1; begin
delete_demo_pkg.oldvals(i).a := :old.a;
delete_demo_pkg.oldvals(i).b := :old.b;
delete_demo_pkg.oldvals(i).c := :old.c;
delete_demo_pkg.cnt := i;
end;
/
create or replace trigger delete_demo_ad
after delete on delete_demo
begin
for i in 1 .. delete_demo_pkg.cnt loop
insert into delete_demo ( a, b, c, hidden_date )
values
( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b,
delete_demo_pkg.oldvals(i).c, sysdate );
end loop;
end;
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
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 Mon Nov 10 1997 - 00:00:00 CET