触发器错误总结一

首先，创建一个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确实还有另外两个触发器。