首先,创建一个mylog表,记录对student表的操作信息:
SQL> CREATE TABLE mylog(
2 curr_user varchar2(20),curr_date date,action char(1))
3 TABLESPACE cjexample
4 PCTFREE 10
5 PCTUSED 40
6 STORAGE(
7 INITIAL 50K
8 NEXT 50K
9 );然后,创建语句级触发器,让mylog表记录对student表的所有操作信息。SQL> CREATE OR REPLACE TRIGGER stu_log
2 AFTER INSERT OR DELETE OR UPDATE ON student
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO mylog VALUES(user,sysdate,'I');
6 ELSIF DELETING THEN
7 INSERT INTO mylog VALUES(user,sysdate,'D');
8 ELSE
9 INSERT INTO mylog VALUES(user,sysdate,'U');
10 END IF;
11 END;最后,测试对student表所做的更改,mylog能否记录操作信息:SQL> update student set stuname='liushun' where stuno=112;1 row updated.更新操作正常。SQL> delete from student where stuno=1;
delete from student where stuno=1
*
ERROR at line 1:
ORA-04091: table CHENJIE.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "CHENJIE.DELE_TGR", line 2
ORA-04088: error during execution of trigger 'CHENJIE.DELE_TGR'删除时报错。SQL> insert into student values(111,'sys','m',sysdate);
insert into student values(111,'sys','m',sysdate)
*
ERROR at line 1:
ORA-04091: table CHENJIE.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "CHENJIE.INS_TGR", line 2
ORA-04088: error during execution of trigger 'CHENJIE.INS_TGR'插入时也报错。再查看mylog时只有一条记录:SQL> select * from mylog;CURR_USER CURR_DATE A
-------------------- -------------- -
CHENJIE 10-8月 -09 U请哪位大侠帮我分析分析:
SQL> CREATE TABLE mylog(
2 curr_user varchar2(20),curr_date date,action char(1))
3 TABLESPACE cjexample
4 PCTFREE 10
5 PCTUSED 40
6 STORAGE(
7 INITIAL 50K
8 NEXT 50K
9 );然后,创建语句级触发器,让mylog表记录对student表的所有操作信息。SQL> CREATE OR REPLACE TRIGGER stu_log
2 AFTER INSERT OR DELETE OR UPDATE ON student
3 BEGIN
4 IF INSERTING THEN
5 INSERT INTO mylog VALUES(user,sysdate,'I');
6 ELSIF DELETING THEN
7 INSERT INTO mylog VALUES(user,sysdate,'D');
8 ELSE
9 INSERT INTO mylog VALUES(user,sysdate,'U');
10 END IF;
11 END;最后,测试对student表所做的更改,mylog能否记录操作信息:SQL> update student set stuname='liushun' where stuno=112;1 row updated.更新操作正常。SQL> delete from student where stuno=1;
delete from student where stuno=1
*
ERROR at line 1:
ORA-04091: table CHENJIE.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "CHENJIE.DELE_TGR", line 2
ORA-04088: error during execution of trigger 'CHENJIE.DELE_TGR'删除时报错。SQL> insert into student values(111,'sys','m',sysdate);
insert into student values(111,'sys','m',sysdate)
*
ERROR at line 1:
ORA-04091: table CHENJIE.STUDENT is mutating, trigger/function may not see it
ORA-06512: at "CHENJIE.INS_TGR", line 2
ORA-04088: error during execution of trigger 'CHENJIE.INS_TGR'插入时也报错。再查看mylog时只有一条记录:SQL> select * from mylog;CURR_USER CURR_DATE A
-------------------- -------------- -
CHENJIE 10-8月 -09 U请哪位大侠帮我分析分析:
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.注意:红色字体TRIGGER里DML记得COMMIT,如果TRIGGER里不COMMIT不是没意义了吗?
可是dele_tgr和ins_tgr根本不存在啊!
那为什么update的时候可以呢?
trigger里的DML会随触发触发器的DML一起COMMIT。
这样查一下看有没有数据:select * from all_objects t where t.object_name = 'DELE_TGR' and t.object_type = 'TRIGGER' and t.owner = 'CHENJIE';
select * from all_objects t where t.object_name = 'INS_TGR' and t.object_type = 'TRIGGER' and t.owner = 'CHENJIE';
select * from dba_triggers WHERE owner = 'CHENJIE' AND table_name = 'STUDENT'附上可以重现楼主错误的测试SQL:
CREATE TABLE mylog(curr_user varchar2(20),curr_date date,action char(1));
CREATE TABLE student(stuno number, stuname VARCHAR2(30),gender CHAR(1),edu_date DATE); CREATE OR REPLACE TRIGGER stu_log
AFTER INSERT OR DELETE OR UPDATE ON student
BEGIN
IF INSERTING THEN
INSERT INTO mylog VALUES(user,sysdate,'I');
ELSIF DELETING THEN
INSERT INTO mylog VALUES(user,sysdate,'D');
ELSE
INSERT INTO mylog VALUES(user,sysdate,'U');
END IF;
END; CREATE OR REPLACE TRIGGER INS_TGR
AFTER INSERT ON student
FOR EACH ROW
declare
v_num NUMBER;
BEGIN
SELECT COUNT(*) INTO v_num FROM student WHERE stuno = :old.stuno;
END; CREATE OR REPLACE TRIGGER DELE_TGR
BEFORE DELETE ON student
FOR EACH ROW
declare
v_num NUMBER;
BEGIN
SELECT COUNT(*) INTO v_num FROM student WHERE stuno = :old.stuno;
END;--student无数据
TRUNCATE TABLE student;
TRUNCATE TABLE mylog;
SELECT COUNT(*) FROM student;
update student set stuname='liushun' where stuno=112;
delete from student where stuno=1;
insert into student values(111,'sys','m',sysdate);
select * from mylog;
--student有数据
COMMIT;
TRUNCATE TABLE student;
TRUNCATE TABLE mylog;
ALTER TRIGGER stu_log DISABLE ;
ALTER TRIGGER INS_TGR DISABLE ;
ALTER TRIGGER DELE_TGR DISABLE ;
insert into student values(112,'liushun','m',sysdate);
insert into student values(1,'beyondme','m',sysdate);
COMMIT;
SELECT COUNT(*) FROM student;
ALTER TRIGGER stu_log ENABLE ;
ALTER TRIGGER INS_TGR ENABLE ;
ALTER TRIGGER DELE_TGR ENABLE ;
update student set stuname='liushun' where stuno=112;
delete from student where stuno=1;
insert into student values(111,'sys','m',sysdate);
select * from mylog;
select * from user_triggers where table_name='STUDENT';