HardBirch

触发器错误总结一

时间:09-08-11 栏目:安卓学习笔记 作者:张飞不张,文采横飞 评论:0 点击: 1,199 次

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

声明: 本文由( 张飞不张,文采横飞 )原创编译,转载请保留链接: 触发器错误总结一

触发器错误总结一:等您坐沙发呢!

发表评论


QQ群互动

Linux系统与内核学习群:194051772

WP建站技术学习交流群:194062106

魔豆之路QR

魔豆的Linux内核之路

魔豆的Linux内核之路

优秀工程师当看优秀书籍

优秀程序员,要看优秀书!

赞助商广告

友荐云推荐