OraclePL/SQL触发器(trigger)学习笔记

触发器也是一种带名的PL/SQL块。触发器类似于过程和函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似，

1、触发器的概念

触发器也是一种带名的PL/SQL块。触发器类似于过程和函数，因为它们都是拥有声明、执行和异常处理过程的带名PL/SQL块。与包类似，触发器必须存储在数据库中并且不能被块进行本地化声明。

对于触发器而言，当触发事件发生的时候就会显式地执行该触发器，并且触发器不接受参数。

创建触发器的语法如下

view plaincopy to clipboardprint?

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} triggering_event

[referencing_clause]

[WHEN trigger_condition]

[FOR EACH ROW]

Trigger_body;

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} triggering_event

[referencing_clause]

[WHEN trigger_condition]

[FOR EACH ROW]

Trigger_body;

其中referencing_clause子句的用途是通过一个不同的名称，引用当前正在被更新的记录行中的数据。WHEN子句中的trigger_condition—如果出现—就应该首先执行判断，只有当这个条件值为真的时候，才会执行触发器的主体代码。

2、DML触发器的激活顺序

1）执行before语句级触发器—如果存在这种触发器

2）对受该语句影响的每一行记录

执行before行级触发器—如果存在这种触发器

执行该语句本身

执行after行级触发器--如果存在这种触发器

3）执行after语句级触发器--如果存在这种触发器

同一种类型的触发器的点火次序没有经过定义。如果该次序很重要的话，那么建议将所有这些操作组合到一个触发器当中。

3、行级触发器中的关联标识符

触发器的激活语句每处理一行数据，行级触发器就会激活一次。可以在这种行级触发器内部，访问正被处理的记录行中的数据。这是通过两个关联标识符--:old和:new—实现的。关联标识符也是PL/SQL的一种特殊的绑定变量。标识符前面的冒号，既说明这二者都是绑定变量，同时也说明它们不是一般的PL/SQL变量。PL/SQL编译器会将它们看作下面这个类型的记录：

Triggering_table%ROWTYPE

其中triggering_table是在其上定义触发器的表名。于是，下面这种引用

:new.field

就只有当其中的field是该触发表中的字段名时才会有效。

触发语句

:old

:new

INSERT

未定义—所有字段均为NULL

触发语句完成的时候，要插入的值

UPDATE

更新以前相应记录行的原始值

触发语句完成的时候，要更新的值

DELETE

删除以前相应记录行的原始值

未定义—所有字段均为NULL

注意：INSERT语句上没有定义:old标识符，，DELETE语句上也没有定义:new标识符。如果再INSERT语句上使用:old标识符，或者在DELETE语句上使用:new标识符，PL/SQL并不会产生错误，但是这两个字段值都会为NULL。

伪记录

虽然在语法构成上，会将:new和:old看作triggering_table%ROWTYPE类型的记录，但是，实际上它们并不是记录。因此，那些能够在记录上正常执行的操作，并不能在:new和:old上执行。例如，不能将它们作为一个整体进行赋值。只能对其中的各个字段分别赋值。

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER TempDelete

BEFORE DELETE ON temp_table

FOR EACH ROW

DECLARE

v_TempRec temp_table%ROWTYPE;

BEGIN

/* This is not a legal assignment, since :old is not truly

a record. */

v_TempRec := :old;

/* We can accomplish the same thing, however, by assigning

the fields inpidually. */

v_TempRec.char_col := :old.char_col;

v_TempRec.num_col := :old.num_col;

END TempDelete;

/

CREATE OR REPLACE TRIGGER TempDelete

BEFORE DELETE ON temp_table

FOR EACH ROW

DECLARE

v_TempRec temp_table%ROWTYPE;

BEGIN

/* This is not a legal assignment, since :old is not truly

a record. */

v_TempRec := :old;

/* We can accomplish the same thing, however, by assigning

the fields inpidually. */

v_TempRec.char_col := :old.char_col;

v_TempRec.num_col := :old.num_col;

END TempDelete;

/

REFERENCING子句

还可以使用REFERENCING子句，为:old和:new换一个不同的名称。该子句出现在触发事件以后，WHEN子句以前。其语法如下：

REFERENCING [OLD AS old_name] [NEW AS new_name]

在触发器主体中，可以使用:old_name和:new_name分别代替:old和:new。

注意，在REFERENCING子句中关联标识符都不带冒号。

如下面这个例子所示

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER GenerateAuthorID

BEFORE INSERT OR UPDATE ON authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

author_sequence. Since ID is a column in authors, :new.ID

is a valid reference. */

SELECT author_sequence.NEXTVAL

INTO :new_author.ID

FROM dual;

END GenerateAuthorID;

/

CREATE OR REPLACE TRIGGER GenerateAuthorID

BEFORE INSERT OR UPDATE ON authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

author_sequence. Since ID is a column in authors, :new.ID

is a valid reference. */

SELECT author_sequence.NEXTVAL

INTO :new_author.ID

FROM dual;

END GenerateAuthorID;

/

4、WHEN子句

WHEN子句只能在行级触发器中使用。如果在行级触发器的定义中给出了WHEN子句，触发器主体就只对满足WHEN所定义条件的那些记录行执行。WHEN子句的基本形式如下：

WHEN trigger_condition

其中，trigger_condition是一个布尔表达式。每处理一行记录，都会重新判断该表达式的值。

也可以在trigger_condition内部使用:new和:old记录，但是与REFERENCING子句一样，在trigger_condition内部使用:new和:old时，不需要冒号。仅在触发器主体中才需要使用冒号。

5、触发器谓词

可以在触发器内部使用3个布尔函数，判断触发该触发器的到底是什么操作。这3个谓词分别是INSERTING、UPDATING和DELETING.

使用方法如下面这个例子所示

view plaincopy to clipboardprint?

CREATE OR REPLACE TRIGGER LogInventoryChanges

BEFORE INSERT OR DELETE OR UPDATE ON inventory

FOR EACH ROW

DECLARE

v_ChangeType CHAR(1);

BEGIN

/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

IF INSERTING THEN

v_ChangeType := 'I';

ELSIF UPDATING THEN

v_ChangeType := 'U';

ELSE

v_ChangeType := 'D';

END IF;

/* Record all the changes made to inventory in

inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user. */

INSERT INTO inventory_audit

(change_type, changed_by, timestamp,

old_isbn, old_status, old_status_date, old_amount,

new_isbn, new_status, new_status_date, new_amount)

VALUES

(v_ChangeType, USER, SYSDATE,

:old.isbn, :old.status, :old.status_date, :old.amount,

:new.isbn, :new.status, :new.status_date, :new.amount);

END LogInventoryChanges;

/

CREATE OR REPLACE TRIGGER LogInventoryChanges

BEFORE INSERT OR DELETE OR UPDATE ON inventory

FOR EACH ROW

DECLARE

v_ChangeType CHAR(1);

BEGIN

/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */

IF INSERTING THEN

v_ChangeType := 'I';

ELSIF UPDATING THEN

v_ChangeType := 'U';

ELSE

v_ChangeType := 'D';

END IF;

/* Record all the changes made to inventory in

inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user. */

INSERT INTO inventory_audit

(change_type, changed_by, timestamp,

old_isbn, old_status, old_status_date, old_amount,

new_isbn, new_status, new_status_date, new_amount)

VALUES

(v_ChangeType, USER, SYSDATE,

:old.isbn, :old.status, :old.status_date, :old.amount,

:new.isbn, :new.status, :new.status_date, :new.amount);

END LogInventoryChanges;

/

6、INSTEAD-OF触发器

INSTEAD-OF触发器仅可以定义在视图上（关系型的或对象），并且它们可以替代点火它们的DML语句进行点火。INSTEAD-OF触发器必须是行级的。

7、触发器的限制

触发器的主体是一个PL/SQL块。在PL/SQL块中可以使用的所有语句在触发器主体中都是合法的，但是要受到下面限制的约束：

触发器不应该使用事务控制语句—COMMIT、ROLLBACK或SAVEPOINT。触发器作为触发语句执行的一部分被点火，它和触发语句在同一个事务中。当触发语句被提交或撤回提交时，触发器的工作也相应被提交会撤回提交。

由触发器主体调用的任何过程和函数都不能使用事务控制语句。

触发器主体不能声明任何LONG或者LONG RAW变量。而且，:new和:old不能指向定义触发器的表中的LONG和LONG RAW列。

触发器主体可以访问的表有所限制。

触发器P-Code

当包或者子程序存储在数据字典中时，存储的除了该对象的源代码还有经过编译的p-code。但是对于触发器来说就不是这样的。在数据字典中唯一存储的是触发器的源代码，而不是p-code。结果，每次当从数据字典中重新读出触发器时，必须要进行编译。这对触发器的定义和使用的方式不会带来什么影响，但是会影响触发器的性能。

8、系统触发器

我们前面所看到的DML触发器和INSTEAD-OF触发器都是基于DML事件。而另一方面，系统触发器的激活则是基于两种不同的事件：DDL事件或数据库事件。DDL事件包括CREATE、ALTER或DROP语句，而数据库事件包括数据库服务器的启动/关闭事件，用户的登陆/断开事件，以及服务器错误。创建系统触发器的语法如下：

view plaincopy to clipboardprint?

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name

{BEFORE | AFTER}

{ddl_event_list | database_event_list}

ON {DATABASE | [schema.]SCHEMA}

[when_clause]

Trigger_body;

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name

{BEFORE | AFTER}

{ddl_event_list | database_event_list}

ON {DATABASE | [schema.]SCHEMA}

[when_clause]

Trigger_body;

其中，ddl_event_list是由OR关键字隔开的一个或个DDL事件，database_event_list则是由OR关键字隔开的一个或多个数据库事件。

注意：不能创建INSTEAD-OF系统级触发器。

通过子句ON {DATABASE | [schema.]SCHEMA}我们可以指定这个系统触发器是定义在数据库级上还是模式级上。只要发生了激活事件，数据库级触发器就会激活。而只有激活事件发生在某个具体模式中，相应的模式级触发器才会激活。如果使用SCHEMA关键字的时候没有定义某个具体模式的名称，那么默认设置为拥有这个触发器的模式。

9、修改触发器状态和删除触发器

启动或禁用触发器

ALTER TRIGGER trigger_name {DISABLE | ENABLE};

删除触发器

DROP TRIGGER trigger_name;

还可以使用ALTER TABLE命令，并附加使用ENABLE ALL TRIGGERS或DISABLE ALL TRIGGERS子句，同时将某一个表上的所有触发器开启或关闭。

ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;

可以通过user_triggers来查看相应触发器信息。

10、变化表和限制表

触发器主体（trigger body）可以访问的表和列上有一些限制。在定义这些限制以前，我们先看两个概念—变化表和限制表。

“变化表”（mutating table）是被DML语句正在修改的表。对于触发器而言，它就是定义触发器的表。需要作为DELETE CASCADE参考完整性限制（referential integrity constraints）的结果进行更新的表也是变化的（mutating）。

“限制表”（constraining table）是可能需要对参考完整性限制执行读操作的表。

为了更好的理解定义，我们看下面这个例子

view plaincopy to clipboardprint?

CREATE TABLE registered_students (

student_id NUMBER(5) NOT NULL,

department CHAR(3) NOT NULL,

course NUMBER(3) NOT NULL,

grade CHAR(1),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),

CONSTRAINT rs_student_id

FOREIGN KEY (student_id) REFERENCES students (id),

CONSTRAINT rs_department_course

FOREIGN KEY (department, course)

REFERENCES classes (department, course)

);

--...

CREATE TABLE registered_students (

student_id NUMBER(5) NOT NULL,

department CHAR(3) NOT NULL,

course NUMBER(3) NOT NULL,

grade CHAR(1),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),

CONSTRAINT rs_student_id

FOREIGN KEY (student_id) REFERENCES students (id),

CONSTRAINT rs_department_course

FOREIGN KEY (department, course)

REFERENCES classes (department, course)

);

--...

registered_students有两个声明的参考完整性限制。

Students和classes都是registered_students的限制表。

触发器主体中的SQL不允许进行：

读取或修改触发语句（triggering statement）的任何变化表。这些表包括触发表（triggering table ）自己。

读取或修改触发表（triggering table）的限制表中的主键（primary）、唯一列值（unique）或外键（foreign key）列。但是如果需要的话，可以修改其他列。