触发器详解

进度表：时间主题

80 分钟讲演

40 分钟练习 120

分钟总共

目标

完成本课程后, 您应当能够：?描述不同触发器类型的区别?描述数据库触发器和它们的用途?创建数据库触发器

?描述数据库触发器的点火(firing ) 规则?

删除触发器

本课目标

在本课中，你将学习怎样创建和使用数据库触发器。

触发器的类型

触发器：?是一个PL/SQL 块或是与表、视图方案或数据库关联的PL/SQL 过程

?当特定的事件发生时隐式执行?

触发器可以是：

–应用程序触发器：当一个特定应用程序的事件发生时触发

–

数据库触发器：当数据事件(例如DML) 在一个方案上发生或系统事件(例如登录或关闭数据库) 在数据库上发生时触发

触发器的类型

在一个应用程序中，当一个特殊的数据操纵语言 (DML) 事件发生时，应用程序触发器隐式地执行。一个广泛使用触发器的应用程序的例子是用 Oracle Forms Developer 开发的应用程序。

当一个表上的象 DML 这样的数据事件 (INSERT 、UPDATE 或 DELETE 触发语句)，或在一个视图上的 INSTEAD OF 触发器，或象 CREATE 和 ALTER 这样的数据定义语言 (DDL) 语句被发布时，数据库触发器隐式地执行，不论哪一个用户被连接或哪一个应用程序被使用。当一些用户行为或数据库系统行为发生时，例如，当用户登录时，或 DBA 关闭 (shutdown) 数据库时，数据库触发器也会隐式地执行。

注：数据库触发器可以被定义在表或视图上。如果一个 DML 操作在一个不可更新的视图上被发布，INSTEAD OF 触发器定义那些会发生的行为。如果这些行为包含对表的 DML 操作，那么基于该表的任何触发器都被触发。

数据库触发器可以是在数据库或方案上的系统触发器。如果是数据库，触发器对所有用户的每个事件触发；如果是方案，触发器对指定用户的每个事件触发。

本章讲述创建数据库触发器。创建基于系统事件的数据库触发器在 “更多触发器概念” 一章中讨论。

设计触发器的原则

设计触发器为了：

–执行相关的动作–

集中全局操作

?下面的情况不用触发器：

–已经内建于Oracle 服务器中的功能–

重复其它的触发器

?创建存储过程并且在触发器中调用它们，如果PL/SQL 代码非常长。

过分地使用触发器可能导致复杂的依赖，这在大的应用程序中可能会产生维护困难。

设计触发器的原则

用触发器保证当一个指定的操作被执行时，相关的动作被执行。

数据库触发器只用于那些对触发语句应该被触发的集中的全局的操作，而不管那个

用户或那个应用程序发布了该语句。

不要对重复的或替代已经内建预 Oracle 数据库中的功能定义触发器。例如不要定

义用于实现那些用声明约束可以就可以做到的完整性规则触发器。下面是商业规则设计顺序：

– 使用在 Oracle 数据库中内建的约束，例如，主见、外键等等

– 开发数据库触发器或在中间层开发象 servlet 或 EJB 这样的应用程序

– 如果不能开发上面提到的商业规则，则应开发表示层规则，使用象 Oracle

Forms 、动态 HTML 、JSP 等这样的表示层接口

过多使用触发器可能会导致复杂的依赖关系，这可能会造成维护大型应用程序的困

难。只在必要时使用触发器，并且避免递归和级联效应。

如果触发器的算法很长，将算法创建于存储过程中，再在触发器中调用它们。注意数据库触发器对于每个用户每次触发器被创建的事件发生时触发。

数据库触发器的例子

在这个例子中，数据库触发器 CHECK_SAL 在应用程序尝试插入一行到EMPLOYEES 表中时检查薪金值。按照工作岗位的不同薪金值如果超出了范围就会被拒绝，或者可能被允许但记录在审计表中。

创建DML 触发器

?一个触发器语句包含：?

触发时间

–

对于表：BEFORE, AFTER –对于视图：INSTEAD OF

?触发事件：INSERT, UPDATE 或DELETE ?表名：On table, view ?触发器类型：Row 或statement ?WHEN 字句：限制条件?

触发器体：PL/SQL 块

数据库触发器

在写触发器代码之前，确定触发器各部分的值：触发时机、触发事件和触发类型。部分说明

可能值

触发器时机

当触发器涉及的触发事件时的触发时机

BEFORE AFTER

INSTEAD OF 触发事件

在表或视图上的哪一个数据操纵操作引发了触发器触发 INSERT UPDATE DELETE

触发器类型触发器体被执行多少次 Statement Row

触发器体触发器执行的那些动作

PL/SQL 块

如果在一个表上定义了多个触发器，要意识到相同类型的多个触发器的触发顺序是随机的。为了保证相同类型的多个触发器以特定的顺序触发，统一多个触发器到一个触发器中，再以想要的顺序分别调用过程。

DML 触发器的组成部分

触发时间：什么时候触发器应该触发？

?BEFORE:在表上触发DML 事件之前执行触发器体。?AFTER: 在表上触发DML 事件之后执行触发器体。?

NSTEAD OF:执行触发器体代替触发语句。用于不能以别的方式修改的视图。

BEFORE 触发器

这种类型的触发器通常用于下列情况：

为了确定是否触发器语句应该被允许完成 (这种情况使你能够消除触发器语句的不

必要的处理以及万一在触发动作中发生异常它可能的回滚)。为了在完成一个触发 INSERT 或 UPDATE 语句之前得到列值。为了初始化全局变量或标志，并且验证复杂的商业规则。 AFTER 触发器

这种触发器类型经常用在下面的情形：

为了在执行触发动作之后完成触发语句。

为了在相同的触发语句上实现不同的动作。如果一个 BEFORE 触发器已经存在。 INSTEAD OF 触发器

这种类型的触发器被用于提供一种直接通过 DML 语句透明地修改视图的方式，因为视图是不能被修改的。

你可以对视图写 INSERT 、UPDATE 和 DELETE 语句。INSTEAD OF 触发器在后台不可见地执行动作代码直接作用于基表。

DML 触发器的组成部分

?INSERT ?UPDATE ?

DELETE

触发用户事件：哪一个DML 语句引起触发器执行？你可以用下面任何一个：

触发事件

触发事件或语句可以是在表上的 INSERT 、UPDATE 或 DELETE 语句。

当触发事件是一个 UPDATE 语句时，你可以用一个字段列表来确定那些必需触发

触发器自来改变的列。你不能为 INSERT 或 DELETE 语句指定字段列表，因为它们总是影响整个行。

. . . UPDATE OF salary . . .

触发事件可以是一个、两个或全部 DML 语句的三个。

. . . INSERT 或 UPDATE 或 DELETE

. . . INSERT 或 UPDATE OF job_id . . .

DML 触发器的组成部分

?语句：触发器体对于触发事件执行一次，这是默认。一个语句触发器触发一次，即使根本没有行受影响。?

行：触发体对受触发事件影响的每行执行一次。如果触发事件没有受影响的行，行触发器不执行。

触发器类型：触发器体将对语句影响的每行都执行，还是只执行一次？

语句触发器和行触发器

你可以指定对受触发语句影响的每一行执行一次的行触发器 (例如多行

UPDATE ) ，或对于触发语句，不管它影响多少行，只执行一次的语句触发器。语句触发器

一个语句触发器对于触发事件被触发一次，即使根本没有行受影响。

如果触发器动作不依赖来自受影响的行的数据，或不依赖由触发事件本身提供的数据时，语句触发器是有用的。例如，一个完成对当前用户的复杂安全检查的触发器。行触发器

每当表被触发器事件影响时，一个行触发器触发。如果没有行受触发事件的影响，行触发器不执行。

如果触发器动作依赖于那些受影响的行的数据，或以来于由触发事件本身提供的数据时，行触发器是有用的。

DML 触发器的组成部分

触发器体：触发器将执行的哪些动作？触发器体是一个PL/SQL 块或对一个过程的调用。

触发器体

触发器动作定义那些再触发事件被发布时需要被做的事情。 PL/SQL 块可以包含 SQL 和 PL/SQL 语句，并且可以定义变量、光标、表达式等 PL/SQL 结构。你也可以调用一个 PL/SQL 过程或一个 Java 过程。

另外，行触发器用相关的名字来访问由触发器处理的行的 old 和 new 列值。

注：触发器的大小不能超过 32 K 。

触发动作

…

创建行或语句触发器

根据触发器是对每一受触发语句影响的行必须触发一次还是对触发语句只执行一次而不管受影响行数的需求来确定创建语句触发器还是行触发器。

当触发数据操纵语句影响一个单个的行时，语句触发器和行触发器都只触发一次。

例

用该 SQL 语句不能区分语句触发器和行触发器，因为用该语法只有一行被插入到表中。

创建行或语句触发器(续)

当触发数据操纵语句影响多行时，语句触发器只触发器一次，而行触发器对受语句影响的每一行触发一次。

例

在上面的幻灯片中 SQL 语句引发一个触发次数等于满足 WHERE 子句的行数的行级触发器，在这里满足 WHERE 子句的行数是部门 30 中的雇员数。

创建语句触发器的语法

trigger name是触发器的名字

timing当触发器涉及触发事件时指示触发时机：

BEFORE

AFTER

event指示引起触发器触发的数据操纵操作：

INSERT

UPDATE [OF column]

DELETE

table/view_name指示与触发器相关联的表

trigger body定义由触发器执行的动作的触发器体，既可以用 DECLARE

or BEGIN开始，也可以调用一个过程

触发器名字在同方案中对于其它触发器必须唯一的。触发器名字不需要对于其它方案对象，例如表、视图和过程唯一。

在触发器中连同UPDATE 子句一起使用列名可以增进性能，因为仅当特殊的列被更新时触发器才触发，这样就避免了触发器在其它的列被更新时的无意义触发。

创建 DML语句触发器

如果一个确定的条件被违反，你可以创建一个BEFORE 语句触发器来防止随后的触发操作。

例如，可以创建一个触发器来限制只能在星期一到星期五的上班时间插入EMPLOYEES 表。

如果一个用户在星期六试图插入一行到EMPLOYEES 表中，该用户会看到信息，触发器失败，并且触发语句被回滚。RAISE_APPLICATION_ERROR 是一个服务端内建过程，它返回一个错误给用户并导致PL/SQL 块失败。

当一个数据库触发器失败时，触发语句被 Oracle 服务器自动回滚。

例

在下班时间插入一行到EMPLOYEES 表中。当日起和时间在触发器指定的上班时间之外时，你会得到如幻灯片中所示的错误信息。

组合触发事件

你可以在触发器体中用专用的条件谓词INSERTING、UPDATING 和DELETING 的优势组合几个触发事件为一个。

例

创建一个触发器来限制所有在EMPLOYEES 表上的数据操纵语句事件为星期一到星期五的确定的上班时间。

创建行触发器的语法

trigger_name是触发器的名字

timing当触发器涉及触发事件时指示触发时机：

BEFORE

AFTER

event指示引起触发器触发的数据操纵操作：

INSERT

UPDATE [OF column]

DELETE

table_name指示与触发器相关联的表

REFERENCING 指示当前行的 old 和 new值的相关的名字

(即别名，默认值是OLD 和NEW)

FOR EACH ROW 指示触发器是行触发器

WHEN 指示触发器约束；(条件谓词必须放在圆括号中并且对每一

行都要检测以决定触发器体是否执行)

trigger body定义由触发器执行的动作的触发器体，既可以用 DECLARE

or BEGIN开始，也可以调用一个过程

创建一个行触发器

你可以创建一个BEFORE 行触发器，在一个特定的条件被违反时防止随后的触发操作。

创建一个触发器只允许特定的雇员可以挣高于 15,000 的薪金。如果一个用户试图这样做，触发器唤起一个错误。

UPDATE employees

SET salary = 15500

WHERE last_name = ’Russell’;

使用OLD 和NEW 限定词

在一个行触发器中，在数据改变之前或之后，可以用OLD and NEW 限定词作为前缀引用一个列的值。

值

数据操纵 OLD

值 NEW INSERT NULL 插入的值

UPDATE 更新前的值更新后的值

DELETE 删除前的值NULL

OLD 和NEW 限定词只在行触发器中可用。

在每一个 SQL 和 PL/SQL 语句中用冒号 (:) 前缀这些限定词。

如果这些限定词在WHEN 限制条件中被引用则无冒号 (:) 前缀。

注：如果你在较大的表上做许多的更新，行触发器可能会降低性能。

使用OLD 和NEW 限定词：使用AUDIT_EMP_TABLE 的例子

在EMPLOYEES 表上创建一个触发器来添加行到用户表AUDIT_EMP_TABLE，记录对用户对 EMPLOYEES 表的行为。该触发器用 OLD 和NEW 限定词带指定的列名记录一些列在数据改变之前和改变之后的值。

在AUDIT_EMP_TABLE 表中有一个附加的列COMMENTS 没有在幻灯片中显示。