create or replace trigger step_4_5
before update of maxcalls on cell
for each row
declare
tempcp smallint;
allmc int;
begin
if(:NEW.maxcalls>0) then
select sum(maxcalls)
into allmc
from cell;
allmc:=allmc-:OLD.maxcalls+:NEW.maxcalls;
if(allmc>30) then
select count(*) into tempcp
from telephone
where phonestate='Active' and
x<:OLD.x1 and
x>:OLD.x0 and
y<:OLD.y1 and
y>:OLD.y0;
if(tempcp>=:NEW.maxcalls) then
:NEW.maxcalls:=tempcp;
end if;
else
:NEW.maxcalls:=:OLD.maxcalls;
end if;
end if;
end;创建过程没错,但是在插入的时候就报错了,ORA-04091: table SYSTEM.CELL is mutating, trigger/function may not see it我觉得被修改的数据求和那儿有错,但是我不知道还有什么方法可以进行替换,简单描述如下:
A(a0,a1),a1是smallint型,如果A表中的sum(a1)<30,则不进行更新,否则进行更新,trigger的目标是a1,事件是update.
我会这样写:
create or replace trigger example
before update of a1 on A
declare
tempsum int;
begin
select sum(a1)
into tempsum
from A;
if......
end if;
end;执行update A set a1=a1-5;的时候就会出问题,虽然trigger建立的时候没什么问题
before update of maxcalls on cell
for each row
declare
tempcp smallint;
allmc int;
begin
if(:NEW.maxcalls>0) then
select sum(maxcalls)
into allmc
from cell;
allmc:=allmc-:OLD.maxcalls+:NEW.maxcalls;
if(allmc>30) then
select count(*) into tempcp
from telephone
where phonestate='Active' and
x<:OLD.x1 and
x>:OLD.x0 and
y<:OLD.y1 and
y>:OLD.y0;
if(tempcp>=:NEW.maxcalls) then
:NEW.maxcalls:=tempcp;
end if;
else
:NEW.maxcalls:=:OLD.maxcalls;
end if;
end if;
end;创建过程没错,但是在插入的时候就报错了,ORA-04091: table SYSTEM.CELL is mutating, trigger/function may not see it我觉得被修改的数据求和那儿有错,但是我不知道还有什么方法可以进行替换,简单描述如下:
A(a0,a1),a1是smallint型,如果A表中的sum(a1)<30,则不进行更新,否则进行更新,trigger的目标是a1,事件是update.
我会这样写:
create or replace trigger example
before update of a1 on A
declare
tempsum int;
begin
select sum(a1)
into tempsum
from A;
if......
end if;
end;执行update A set a1=a1-5;的时候就会出问题,虽然trigger建立的时候没什么问题
但是功能完成不了,不知道为什么
Doc ID: 132569.1 Type: PROBLEM
Modified Date : 21-FEB-2008 Status: PUBLISHED
"Checked for relevance on 21-Feb-2008" Problem Description
-------------------You want to do an insert into a table that has a BEFORE row Trigger.When you hard code the values into the INSERT statement, the trigger works fine.For example:
INSERT
INTO content (cont_name,cont_seg,cat_seq)
VALUES('blah',100,200); 1 row created.However, your trigger errors with ERROR ORA-4091 with
INSERT INTO...select statement: INSERT
INTO content (cont_name,cont_seq,cat_seq) (select....from category); ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not see it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'
TRIGGER:CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
DECLARE
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :new.cont_sort IS NULL THEN
:new.cont_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual;
END;
Explanation
-----------Error: ORA 4091
Text: table %s.%s 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.Explanation:
You cannot look at or modify the table that is mutating.Note:
From the Application Developers Guide
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers.
INSERT statements that involve more than 1 row are not considered
single row inserts."
"INSERT INTO <table_name> SELECT ..." are not considered single row
inserts, even if they only result in 1 row being inserted.
RELATED DOCUMENTS
-----------------Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Database Triggers', page 13-22)Note 19879.1
before update of a1 on A
declare
tempsum int;
begin
select sum(a1)
into tempsum
from A;
if(tempsum<18) then
:NEW.a1=:OLD.a1;
end if;
end;
比如A内容如下:
a0 a1
1 3
2 8
3 9update A set a1=a1-2;后内容就成为
a0 a1
1 1
2 7
3 7
但我想要的结果是,对于被一行的update,他都做一次sum(),而不是第一次触发时的sum(),
我想要的输出是:
a0 a1
1 1
2 7
3 9