首先，创建一个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
初步判断student表还有另外两个触发器dele_tgr和ins_tgr;
解决方法：
SQL> desc user_triggers;
Name Null? Type
----------------------------------------- -------- ----------------------------
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
SQL> select triggername from user_triggers where table_owner='STUDENT';
select triggername from user_triggers where table_owner='STUDENT'
*
ERROR at line 1:
ORA-00904: "TRIGGERNAME": invalid identifier
SQL> select trigger_name from user_triggers where table_owner='STUDENT';
no rows selected
SQL> select trigger_name from user_triggers where table_name='STUDENT';
TRIGGER_NAME
------------------------------
DELE_TGR
INS_TGR
STU_LOG
SQL> drop trigger dele_tgr;
Trigger dropped.
SQL> drop trigger ins_tgr;
Trigger dropped.
可见student确实还有另外两个触发器。