ANBOB™

anbob@ORCL>  create or replace trigger tir_man
  2   after update on anbob.testtri
  3   for each row
  4  begin
  5   if (:new.stat<>'1') then
  6   delete testtri where id=:new.id;
  7   end if;
  8  end;
  9   /

Trigger created.

anbob@ORCL> update testtri set stat=0 where id=3;
update testtri set stat=0 where id=3
       *
ERROR at line 1:
ORA-04091: table ANBOB.TESTTRI is mutating, trigger/function may not see it
ORA-06512: at "ANBOB.TIR_MAN", line 3
ORA-04088: error during execution of trigger 'ANBOB.TIR_MAN'


anbob@ORCL> ! oerr ora 4091
04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
// *Cause: A trigger (or a user defined plsql 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.

--摘自Application Developer's Guide 

ORA-04091: trigger/function may not see it

If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger which is not subject to this restriction, and the trigger.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. 
For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.



anbob@ORCL>  create or replace trigger tir_man
  2   after update on anbob.testtri
  3   for each row
  4  begin
  5   if (:new.stat<>'1') then
  6   DBMS_OUTPUT.PUT_LINE('find a update stat command!');
  7   end if;
  8  end;
  9   /

Trigger created.

anbob@ORCL> update testtri set stat=0 where id=3;
find a update stat command!

1 row updated.

anbob@ORCL> update testtri set stat=1 where id=3;

1 row updated.

anbob@ORCL> create or replace trigger tir_man
  2   after update of stat
  3    on anbob.testtri
  4    for each row
  5  begin
  6   if (:new.stat='1') then
  7    DBMS_OUTPUT.PUT_LINE('find a update stat command!id is '||:new.id);
  8   end if;
  9  end;
 10   /

Trigger created.

anbob@ORCL> update testtri set stat=1 where id=2;
find a update stat command!id is 2

1 row updated.

anbob@ORCL> create or replace trigger tir_man
  2   after update of stat
  3    on anbob.testtri
  4    for each row
  5  begin
  6   if (:new.stat='1') then
  7    DBMS_OUTPUT.PUT_LINE('find a update stat command!id is '||new.id);
  8   end if;
  9  end;
 10   /

Warning: Trigger created with compilation errors.

anbob@ORCL> show err
Errors for TRIGGER TIR_MAN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: Statement ignored
3/61     PLS-00201: identifier 'NEW.ID' must be declared
anbob@ORCL> create or replace trigger tir_man
  2   after update of stat
  3    on anbob.testtri
  4    for each row
  5    when (new.stat='1')
  6  begin
  7    DBMS_OUTPUT.PUT_LINE('find a update stat command!id is '||:new.id);
  8  end;
  9   /

Trigger created.

anbob@ORCL> update testtri set stat=1 where id=2;
find a update stat command!id is 2

1 row updated.

anbob@ORCL> update testtri set stat=0 where id=2;

1 row updated.

–摘自SQL Reference

WHEN Clause
Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger.
See the syntax description of condition in Chapter 7, “Conditions”.
This condition must contain correlation names and cannot contain a query.
The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables,
so are not preceded by a colon (:). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause.

打赏

微信扫一扫，打赏作者吧～