SQL 表正在发生变化，触发器/函数可能看不到它（阻止平均成绩低于 2.5）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16182089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5)
提问by The Rationalist
Here's the problem:
这是问题所在：
Create a trigger that prevents any change to the taking relation that would drop the overall average grade in any particular class below 2.5. Note: This trigger is not intended to address the average GPA of any given student, but rather it should address the average grade for all grades assigned in a particular class.
创建一个触发器，以防止任何会导致任何特定班级的整体平均成绩低于 2.5 的录取关系发生变化。注意：此触发器并非旨在解决任何给定学生的平均 GPA，而是应解决特定班级中分配的所有成绩的平均成绩。
Here's the schema:
这是架构：
Student-schema =(studentnum, name, standing, gpa, major)
Class-schema = (schedulenum, semester, department, classnum, days, time, place, enrollment)
Instructor-schema = (name, department, office)
Teaches-schema = (name, schedulenum, semester)
Taking-schema = (studentnum, schedulenum, semester, grade)
I'm having a terrible time with these triggers, but here's my attempt to make this work:
这些触发器让我度过了一段糟糕的时光，但这是我尝试进行的工作：
CREATE OR REPLACE TRIGGER stopChange
AFTER UPDATE OR INSERT OR DELETE ON taking
REFERENCING OLD AS old
NEW AS new
FOR EACH ROW
DECLARE
grd_avg taking.grade%TYPE;
BEGIN
SELECT AVG(grade)
INTO grd_avg
FROM taking
WHERE studentnum = :new.studentnum
AND schedulenum = :new.schedulenum
AND semester = :new.semester;
IF grd_avg < 2.5 THEN
UPDATE taking
SET grade = :old.grade
WHERE studentnum = :old.studentnum
AND schedulenum = :old.schedulenum
AND semester = :old.semester;
END IF;
END;
/
I'm obviously doing something wrong because when I then go to update or delete a tuple, I get the error:
我显然做错了什么，因为当我去更新或删除一个元组时，我得到了错误：
ERROR at line 1:
ORA-04091: table TAKING is mutating, trigger/function may not see it
ORA-06512: at "STOPCHANGE", line 6
ORA-04088: error during execution of trigger 'STOPCHANGE'
Any advice? I'm using Oracle.
有什么建议吗？我正在使用甲骨文。
采纳答案by Gordon Linoff
I think you can fix this by rewriting this as a beforetrigger, rather than an aftertrigger. However, this might be a little complicated for inserts and deletes. The idea is:
我认为您可以通过将其重写为前触发器而不是后触发器来解决此问题。但是，这对于插入和删除可能有点复杂。这个想法是：
CREATE OR REPLACE TRIGGER stopChange
BEFORE UPDATE OR INSERT OR DELETE ON taking
REFERENCING OLD AS old
NEW AS new
FOR EACH ROW
DECLARE
grd_avg taking.grade%TYPE;
BEGIN
SELECT (SUM(grade) - oldgrade + new.grade) / count(*)
INTO grd_avg
FROM taking
WHERE studentnum = :new.studentnum
AND schedulenum = :new.schedulenum
AND semester = :new.semester;
IF grd_avg < 2.5 THEN
new.grade = old.grade
END IF;
END;
回答by Sweta
use this statement inside DECLARE, it will work.
在里面使用这个语句DECLARE，它会起作用。
pragma autonomous_transaction;
回答by Art
First you need to read about triggers, mutating table error and compound triggers: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2005
首先，您需要了解触发器、变异表错误和复合触发器：http: //docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2005
Your trigger is AFTER UPDATE OR INSERT OR DELETE. Means if you run UPDATE OR INSERT OR DELETE statements on this table, the trigger will fire. But you are trying to update the same table again inside your trigger, which is compl. wrong. This this is why you are getting the error. You cannot modify the same table the trigger is firing on. The purpose of trigger is to fire automatically when table is updated, inserted or deleted in your case. What you need is some procedure, not trigger.
您的触发器是 AFTER UPDATE OR INSERT OR DELETE。意味着如果您在此表上运行 UPDATE OR INSERT OR DELETE 语句，触发器将触发。但是您正在尝试在触发器内再次更新同一个表，这是 compl。错误的。这就是您收到错误的原因。您不能修改触发器正在触发的同一个表。触发器的目的是在您的案例中更新、插入或删除表时自动触发。你需要的是一些程序，而不是触发器。
回答by acostil
I had the same issue and I noticed that if you do a select on the same table you put the trigger on you may/will get this problem. You can remove FOR EACH ROWor use the data in :Newto do the calculation (if possible) and then make the update.
我有同样的问题，我注意到如果你在同一张桌子上做一个选择，你把触发器放在你可能/会遇到这个问题。您可以删除FOR EACH ROW或使用:New 中的数据进行计算（如果可能），然后进行更新。
In your case it will make more sense to use separate table to have the avg_grade per semester.
在您的情况下，使用单独的表格来获得每学期的 avg_grade 会更有意义。
回答by user2988234
Even we have ended up with same issue in our project. But after searching in few oracle forums, we have found the below solution.
即使我们在我们的项目中也遇到了同样的问题。但是在几个oracle论坛中搜索后，我们找到了以下解决方案。
1)Save the Old/New Column data in a temporary table as pat of row level trigger. 2) Write a statement level trigger and use the data saved in step 1.
1）将旧/新列数据保存在临时表中作为行级触发器的pat。2) 编写语句级触发器，并使用步骤1中保存的数据。
This would solve the issue I think.
这将解决我认为的问题。
回答by user1238353
If you want retrieve other data by join other table(TABLE_ADDRESS). Here is my solution.
如果您想通过加入其他表（TABLE_ADDRESS）来检索其他数据。这是我的解决方案。
CREATE OR REPLACE TRIGGER TRIGGER_TABLE_ACTIVITIES AFTER INSERT ON TABLE_NAME
FOR EACH ROW
DECLARE
V_ADDRESS VARCHAR2(100);
BEGIN
SELECT A.ADDRESS INTO V_ADDRESS
FROM TABLE_ADDRESS A
WHERE A.ADDRESSID = :NEW.ADDRESSID
;
INSERT INTO TABLE_ACTIVITIES(
NAME, ADDRESS)
VALUES(:NEW.NAME, V_ADDRESS);
END;
/
