Question

oracle 触发器的问题。。

触发器能成功创建，但是对student进行update操作的时候显示如下错误。困扰多时，求解
create or replace trigger changeId_before_update
after update of studentID on student
for each row
declare
stuStudentID number;
oStuStudentID number;
begin
select studentID into stuStudentID from student where studentID=:new.studentID;
select studentID into oStuStudentID from student where studentID=:old.studentID;
update borrowRecord set studentID=stuStudentID where studentID=oStuStudentID;
commit;
end;
/

报错如下：
*
ERROR at line 1:
ORA-04091: table HOMEWORK.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "HOMEWORK.CHANGEID_BEFORE_UPDATE", line 5
ORA-04088: error during execution of trigger 'HOMEWORK.CHANGEID_BEFORE_UPDATE'

Answer 1

Quote：
“触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；”

Answer 2

create or replace trigger changeId_before_update
after update of studentID on student
for each row
begin
update borrowRecord
set studentID = :new.studentID
where studentID = :old.studentID;
commit;
end;

Answer 3

首先声明oracle变量和触发器之类的我也不太懂，不过这个问题我以前遇到过，是因为在执行触发器的时候比如说student的表的值改变了，然后就报大概是这个错
我觉得那个给变量赋值的语句能不能改成
select :new.studentID ,:old.studentID into stuStudentID,oStuStudentID from dual
我不知道对不对，你可以试试，我的意思就是给变量赋值的时候就不要涉及到student表了，
碰碰运气

Answer 4

触发器中不能使用数据库事务控制语句