【DB笔试面试448】Oracle中有哪几类触发器？

2019-09-30 19:23:24 浏览数 (1)
题目部分

Oracle中有哪几类触发器？

答案部分

Oracle数据库有4种触发器，分别是DML、替代触发器（INSTEAD OF触发器）、DDL和系统触发器，一般的应用系统中都使用到DML、替代触发器，而DDL和系统触发器是DBA管理数据库用得比较多。下图是Oracle触发器的分类图：

图 3-2 Oracle中触发器的分类

触发器（TRIGGER）的组成主要有以下几部分：

①　触发事件：即在何种情况下触发TRIGGER。例如：DML语句（INSERT、UPDATE和DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句等在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

②　触发时间（触发时机）：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后（AFTER）触发，也就是触发事件和该TRIGGER的操作顺序。如果指定为BEFORE，那么表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，那么表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

③　触发器本身：即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。

④　触发频率：说明触发器内定义的动作被执行的次数，分为语句级（STATEMENT）触发器和行级（ROW）触发器。

a. 语句级（STATEMENT）触发器：是指当某触发事件发生时，该触发器只执行一次；语句级触发器不允许和WHEN子句一起使用。

b. 行级（ROW）触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

c. 当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。

换句话说，语句级触发器针对某一条语句只触发一次，而行级触发器则针对语句所影响的每一行都触发一次。例如：某条UPDATE语句修改了表中的100行数据，那么针对该UPDATE事件的语句级触发器将被触发一次，而行级触发器将被触发100次。

⑤　触发对象：包括表、视图、用户、数据库。只有在这些对象上发生了符合触发条件的触发事件时，才会执行触发操作。

⑥　触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

下面分别介绍这4类触发器。

1、DML触发器

DML触发器由DML语句触发，例如，INSERT、UPDATE和DELETE语句。针对所有的DML事件，按触发的时间可以将DML触发器分为BEFORE触发器和AFTER触发器，另外，DML触发器也可以分为语句级触发器与行级触发器。

创建DML触发器的一般语法如下所示：

代码语言：javascript复制
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] table_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;

其中：

l BEFORE和AFTER指出触发器的触发时机为前触发还是后触发，前触发是在执行触发事件之前触发，后触发是在执行触发事件之后触发当前所创建的触发器。

l FOR EACH ROW选项说明触发器为行级触发器。当省略FOR EACH ROW选项时，BEFORE和AFTER触发器为语句级触发器，而INSTEAD OF触发器则为行级触发器。

l REFERENCING子句说明相关名称，在行级触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。在触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号，但在WHEN子句中则不能加冒号。

l WHEN子句说明触发约束条件。当Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEAD OF行触发器和其它类型的触发器中。

DML触发器有如下几种事件：

1、 INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。

2、 UPDATING[（column_1,column_2,…,column_x）]：当触发事件是UPDATING时，若修改了column_x列，则取值为TRUE，否则为FALSE。其中，column_x是可选的。

3、 DELETING：当触发事件是DELETE时，则取值为TRUE，否则取值为FALSE。

DML触发器有如下的限制条件：

l CREATE TRIGGER语句文本的字符长度不能超过32KB

l 触发器体内的SELECT语句只能为SELECT … INTO … 结构，或者为定义游标所使用的SELECT语句

l 触发器中不能使用事务控制语句COMMIT、ROLLBACK和SAVEPOINT

l 由触发器所调用的存储过程或函数也不能使用数据库事务控制语句

l 触发器中不能使用LONG、LONG RAW类型

l 触发器内不能通过:NEW修改LOB列中的数据

l 触发器最多可以嵌套32层

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前或操作后列的值，这个时候可以使用:NEW或者:OLD来实现。其中，:NEW表示操作完成后列的值，而:OLD表示操作完成前列的值，如下表所示：

特性

INSERT

UPDATE

DELETE

:OLD

NULL

修改前的值

删除前的值

:NEW

插入的值

修改后的值

NULL

:OLD表和:NEW表是内存中的两个表，其结构和源表结构完全一致。

（1） 当插入时，先将值插入到:NEW表中，在没有控制的前提下才真正地插入到表中。

（2） 当删除时，先将要删除的数据移到:OLD表中，以前的表中的数据就没有了。

（3） 当更新时，UPDATE XXX SET XXX=:NEW.XXX WHERE XXX=:OLD.XXX，WHERE条件是判断已有的值，它就移动:OLD表中，SET是设置新的值先放到:NEW表中。

下面举一个行级触发器的例子。该触发器可以实现，当职工表SCOTT.EMP表被删除一条记录时，把被删除记录写到职工表删除日志表中去，代码如下所示：

代码语言：javascript复制
--建立日志表
CREATE TABLE SCOTT.EMP_HIS AS SELECT * FROM SCOTT.EMP WHERE 1=2; 
--建立触发器
CREATE OR REPLACE TRIGGER SCOTT.TR_DEL_EMP 
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON SCOTT.EMP 
   FOR EACH ROW   --说明创建的是行级触发器 
BEGIN
   --将修改前数据插入到日志记录表EMP_HIS ,以供监督使用。
   INSERT INTO EMP_HIS(DEPTNO , EMPNO, ENAME , JOB ,MGR , SAL , COMM , HIREDATE )
   VALUES( :OLD.DEPTNO, :OLD.EMPNO, :OLD.ENAME , :OLD.JOB,:OLD.MGR, :OLD.SAL, :OLD.COMM, :OLD.HIREDATE );
END;

测试行级触发器：

代码语言：javascript复制
SYS@lhrdb> SELECT * FROM SCOTT.EMP_HIS;
no rows selected
SYS@lhrdb> DELETE SCOTT.EMP WHERE EMPNO=7788;
1 row deleted.
SYS@lhrdb> SELECT * FROM SCOTT.EMP_HIS;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

下面再举一个语句级触发器的例子。

代码语言：javascript复制
--建立日志表
CREATE TABLE  SCOTT.MYLOG_LHR
      (CUR_USER VARCHAR2(30), CUR_DATE DATE, ACTION VARCHAR2(30));
--建立触发器
CREATE OR REPLACE TRIGGER SCOTT.TRI_DML_EMP_LHR
  AFTER INSERT OR DELETE OR UPDATE ON SCOTT.EMP
BEGIN
  IF INSERTING THEN
    INSERT INTO SCOTT.MYLOG_LHR
      (CUR_USER, CUR_DATE, ACTION)
    VALUES
      (USER, SYSDATE, 'I');
  ELSIF DELETING THEN
    INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'D');
  ELSE
    INSERT INTO SCOTT.MYLOG_LHR VALUES (USER, SYSDATE, 'U');
  END IF;
END;
/

测试语句级触发器：

代码语言：javascript复制
SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;
no rows selected
SYS@lhrdb> DELETE SCOTT.EMP WHERE ROWNUM<=1;
1 row deleted.
SYS@lhrdb> SELECT * FROM SCOTT.MYLOG_LHR;
CUR_USER                       CUR_DATE            ACTION
------------------------------ ------------------- ------------------------------
SYS                            2016-10-26 14:14:37 D

2、替代触发器（INSTEAD OF触发器）

由于在Oracle里，不能直接对由两个以上的表建立的视图进行操作，所以，给出了替代触发器。可以用替代触发器解决视图的多表更新问题，并且替代触发器只能用于视图。例如：针对INSERT事件的替代触发器，它由INSERT语句触发，当出现INSERT语句时，该语句不会被执行，而是执行替代触发器中定义的语句。

创建替代触发器的一般语法如下所示:

代码语言：javascript复制
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

其中，INSTEAD OF选项使Oracle激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF触发器。其它选项和DML触发器的语法相同。

创建替代触发器需要注意以下几点内容：

①　只能创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

②　不能指定BEFORE或AFTER选项。

③　FOR EACH ROW是可选的，即替代触发器只能是行级触发器，所以，没有必要指定。

④　没有必要在针对一个表的视图上创建替代触发器，只要创建DML触发器就可以了。

⑤　每一个表和视图只能有一个替代触发器。

⑥　替代触发器被用于更新那些没有办法通过正常方式更新的视图。

⑦　替代触发器的主要优点就是可以使不能更新的视图支持更新。它支持多个表中数据的插入、更新和删除操作。

下面举一个视图触发器的例子。首先创建一个含有聚合函数的视图，如下所示：

代码语言：javascript复制
CREATE OR REPLACE VIEW SCOTT.VW_EMP_LHR AS 
SELECT DEPTNO, COUNT(*) TOTAL_EMPLOYEER, SUM(SAL) TOTAL_SALARY
  FROM SCOTT.EMP
 GROUP BY DEPTNO;

在此视图中，当直接删除时会报错，如下所示：

代码语言：javascript复制
SYS@lhrdb> DELETE FROM SCOTT.VW_EMP_LHR WHERE DEPTNO=10;
DELETE FROM VW_EMP_LHR WHERE DEPTNO=10
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view--此视图的数据操纵操作非法

但是可以通过创建替代触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：

CREATE OR REPLACE TRIGGER SCOTT.EMP_VIEW_DELETE

INSTEAD OF DELETE ON SCOTT.VW_EMP_LHR

FOR EACH ROW

BEGIN

DELETE FROM SCOTT.EMP WHERE DEPTNO = :OLD.DEPTNO;

END;

测试替代触发器，如下所示：

SYS@lhrdb> DELETE FROM SCOTT.VW_EMP_LHR WHERE DEPTNO=10;

1 row deleted.

SYS@lhrdb> SELECT * FROM SCOTT.EMP WHERE DEPTNO=10;

no rows selected

3、DDL触发器和系统触发器

由于DDL触发器和系统触发器的创建语法很相似，所以，在此一并讲解。其实，该部分内容涉及到面试中的一个问题，那就是，“如何监控会话的登录登出情况？”，答案就是使用审计或系统触发器来实现。下面将详细讲解该部分的内容。

系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE、ALTER及DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与登出、数据库服务错误等。

值得注意的一点是，隐含参数“_SYSTEM_TRIG_ENABLED”的默认值是TRUE，即允许DDL和系统触发器。当设置隐含参数“_SYSTEM_TRIG_ENABLED”为FALSE的时候，将禁用DDL和系统触发器。

创建系统触发器的一般语法如下所示：

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER}

{DDL_EVENT_LIST | DATABASE_EVENT_LIST}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;

其中：

l DDL_EVENT_LIST：一个或多个DDL事件，事件间用OR分开，可以对所有DDL语句监控（直接写DDL），也可以对个别DDL语句监控。

l DATABASE_EVENT_LIST：一个或多个数据库事件，事件之间用OR分开。

l ON：系统触发器按照作用范围，分为SCHEMA触发器和DATABASE触发器。SCHEMA触发器作用在单个用户上，而DATABASE触发器作用在整个数据库所有用户上。

l 对于WHEN子句后边的内容需要由小括号括起来。

需要注意的是，系统触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式（SCHEMA）之上时，只有模式所指定用户的DDL操作和该用户操作所导致的错误才能激活触发器，默认时为当前用户模式。当建立在数据库（DATABASE）之上时，该数据库所有用户的DDL操作和所有用户操作所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器，要求用户具有ADMINISTER DATABASE TRIGGER权限或具有DBA角色。一般应该赋予的权限包括ADMINISTER DATABASE TRIGGER、ALTER ANY TRIGGER和CREATE ANY TRIGGER。

下表给出了系统触发器的种类和事件出现的时机：

表 3-4系统触发器的种类和事件

事件

允许的时机

说明

STARTUP

AFTER

启动数据库实例之后触发

SHUTDOWN

BEFORE

关闭数据库实例之前触发（非正常关闭不触发）

SERVERERROR

AFTER

数据库服务器发生错误之后触发。事件SERVERERROR可以用于跟踪数据库中发生的错误。其错误代码可以使用触发器内部的SERVER_ERROR属性函数取出。该函数可以让用户确定堆栈中的错误码。然而，该函数不能返回与该错误码相关的错误信息，但是可以通过使用系统函数DBMS_UTILITY.FORMAT_ERROR_STACK来解决。尽管触发器本身不会引发错误，但借助于该过程可以使用PL/SQL来访问错误堆栈

LOGON

AFTER

成功登录连接到数据库后触发

LOGOFF

BEFORE

开始断开数据库连接之前触发

CREATE

BEFORE、AFTER

在执行CREATE语句创建数据库对象之前、之后触发

DROP

BEFORE、AFTER

在执行DROP语句删除数据库对象之前、之后触发

ALTER

BEFORE、AFTER

在执行ALTER语句更新数据库对象之前、之后触发

DDL

BEFORE、AFTER

在执行大多数DDL语句之前、之后触发

GRANT

BEFORE、AFTER

执行GRANT语句授予权限之前、之后触发

REVOKE

BEFORE、AFTER

执行REVOKE语句收权限之前、之后触犯发

RENAME

BEFORE、AFTER

执行RENAME语句更改数据库对象名称之前、之后触犯发

AUDIT/NOAUDIT

BEFORE、AFTER

执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

除DML语句的列属性外，其余事件属性值可通过调用Oracle定义的事件属性函数来读取，参考下表：

表 3-5事件属性函数

函数名称

同义词

数据类型

说明

SYSEVENT

ORA_SYSEVENT

字符串

激活触发器的事件名称

INSTANCE_NUM

ORA_INSTANCE_NUM

数值

数据库实例号

DATABASE_NAME

ORA_DATABASE_NAME

字符串

数据库名称

SERVER_ERROR(POSI)

ORA_SERVER_ERROR

数值

错误信息栈中POSI指定位置中的错误号

IS_SERVERERROR(ERR_NUMBER)

ORA_IS_SERVERERROR

布尔值

检查ERR_NUMBER指定的错误号是否在错误信息栈中，若在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误

LOGIN_USER

ORA_LOGIN_USER

字符串

登陆或注销的用户名称

DICTIONARY_OBJ_TYPE

ORA_DICT_OBJ_TYPE

字符串

DDL语句所操作的数据库对象类型

DICTIONARY_OBJ_NAME

ORA_DICT_OBJ_NAME

字符串

DDL语句所操作的数据库对象名称

DICTIONARY_OBJ_OWNER

ORA_DICT_OBJ_OWNER

字符串

DDL语句所操作的数据库对象所有者名称

DES_ENCRYPTED_PASSWORD

ORA_DES_ENCRYPTED_PASSWORD

字符串

正在创建或修改的经过DES算法加密的用户口令

CLIENT_IP_ADDRESS

ORA_CLIENT_IP_ADDRESS

字符串

用于返回客户端IP地址

上表中的函数和同义词的对应关系可以通过如下的SQL语句找到：

SELECT * FROM DBA_SYNONYMS D WHERE D.SYNONYM_NAME LIKE 'ORA%';

就像DML触发器一样，系统触发器可以使用WHEN子句来指定触发器激活条件。关于系统触发器需要了解以下几点：

l STARTUP和SHUTDOWN触发器不能带有任何条件。

l SERVERERROR触发器可以使用ERRNO测试来检查特定的错误。

l LOGON和LOGOFF触发器可以使用USERID或USERNAME测试来检查用户标识或用户名。

l DDL触发器可以检查正在修改对象的名称、类型和操作类别。

DDL触发器有很多实际用途，如下所示：

①　建表的同时建立公共同义词

②　阻止非授权用户的TRUNCATE操作

③　记录所有的DDL语句（包括SERVERERROR、GRANT、SHUTDDOWN、ALTER、REVOKE、DROP、TRUNCATE、COMMENT、STARTUP、AUDIT、CREATE、ANALYZE），以便查找责任人

④　阻止DDL操作

⑤　记录服务器错误

⑥　填充V$SESSION的CLIENT_INFO和CLIENT_IDENTIFIER列

⑦　记录用户登录数据库失败的详细信息

⑧　监控会话的登录登出情况

要禁用或启用表的所有触发器，可以使用ALTER TABLE语句，如下所示：

ALTER TABLE T_20161026_LHR DISABLE ALL TRIGGERS; --禁用触发器

ALTER TABLE T_20161026_LHR ENABLE ALL TRIGGERS; --启用触发器

将触发器设置为禁用或启用使用ALTER TRIGGER语句，如下所示：

ALTER TRIGGER TRIGGER_NAME ENABLE;

ALTER TRIGGER TRIGGER_NAME DISABLE;

重新编译触发器的语句为：

ALTER TRIGGER [SCHEMA.] TRIGGER_NAME COMPILE;

最后，介绍一下编写触发器的一些注意事项：

l 触发器不接受参数。

l 一个表上最多可以有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。

l 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l 触发器最大为32KB。若确实需要，则可以先建立存储过程，然后在触发器中调用存储过程。

l 在触发器的执行部分只能使用DML语句（例如SELECT、INSERT、UPDATE、DELETE等），不能使用DDL语句（例如CREATE、ALTER、DROP等）。

l 触发器中不能包含事务控制语句（例如COMMIT、ROLLBACK、SAVEPOINT等）。因为触发器是触发语句的一部分，当触发语句被提交、回退时，触发器也被提交、回退了。

l 在触发器主体中调用的任何存储过程、函数，都不能使用事务控制语句。

l 在触发器主体中不能声明任何LONG或BLOB变量。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

数据库 sql rubyonrails 云推荐引擎

0 人点赞