10. 触发器
触发器就像一个存储过程，只要发生指定事件，数据库就会自动调用它。
注意
数据库只能检测系统定义的事件。您无法定义自己的事件。
10.1. 触发器概述
与存储过程一样，触发器是一个命名的 PL/SQL 单元，它存储在数据库中并且可以重复调用。与存储过程不同，您可以启用和禁用触发器，但不能显式调用它。
当触发器被启用时，数据库会自动调用它——也就是说，触发器会触发——只要它的触发事件发生。当触发器被禁用时，它不会触发。
CREATE TRIGGER您使用该语句创建触发器。您可以根据触发语句及其作用的项目来指定触发事件。触发器是在项目上创建或定义的，项目可以是表、视图、模式或数据库。您还可以指定时间点，它确定触发器是在触发语句运行之前还是之后触发，以及是否针对触发语句影响的每一行触发。默认情况下，触发器是在启用状态下创建的。
如果触发器是在表或视图上创建的，则触发事件由 DML 语句组成，触发器称为DML 触发器。
如果触发器是在模式或数据库上创建的，那么触发事件由 DDL 或数据库操作语句组成，触发器称为系统触发器。
条件触发器是一个DML 或系统触发器，它具有一个WHEN子句，该子句指定数据库为触发语句影响的每一行计算的 SQL 条件。
当触发器触发时，触发器引用的表可能正在经历其他用户事务中的 SQL 语句所做的更改。在触发器中运行的 SQL 语句遵循与独立 SQL 语句相同的规则。具体来说： - 触发器中的查询会看到引用表的当前read-consistent物化视图以及在同一事务中更改的任何数据。 - 触发器中的更新在继续之前等待现有数据锁被释放。
INSTEAD OF 触发器: - 在非编辑视图或非编辑视图的嵌套表列上创建的 DML 触发器 - CREATE在语句 上定义的系统触发器
数据库触发 INSTEAD OF 触发器而不是运行触发语句。
另请参阅
10.2. 使用触发器的理由
触发器允许您自定义数据库管理系统。 例如，您可以使用触发器来： - 自动生成虚拟列值 - 记录事件 - 收集有关表访问的统计信息 - 在针对视图发出 DML 语句时修改表数 - 将有关数据库事件、用户事件和 SQL 语句的信息发布到订阅应用程序 - 防止在正常工作时间后对表进行 DML 操作 - 防止无效交易 - 强制执行您无法使用约束定义的复杂业务或参照完整性规则（请参阅“触发器和约束的不同之处”）
触发器和约束有何不同
触发器和约束都可以约束数据输入，但它们有很大的不同。
触发器始终仅适用于新数据。例如，触发器可以阻止 DML 语句将NULL值插入数据库列，但该列可能包含NULL在定义触发器之前或禁用触发器时插入到列中的值。
约束可以仅应用于新数据（如触发器）或同时应用于新数据和现有数据。约束行为取决于约束状态。
与强制执行相同规则的触发器相比，约束更容易编写且不易出错。但是，触发器可以强制执行一些约束不能执行的复杂业务规则。强烈建议您仅在以下情况下使用触发器来约束数据输入： - 强制执行无法使用约束定义的复杂业务或参照完整性规则
另请参阅
-
《数据库开发指南》,了解有关使用约束来强制执行业务规则和防止将无效信息输入到表中的信息
-
"确保引用完整性的触发器",了解有关使用触发器和约束来维护父表和子表之间的引用完整性的信息
10.3. DML 触发器
DML 触发器在表或视图上创建，其触发事件由 DML 语句 DELETE、INSERT 和 UPDATE 组成。
要创建响应 MERGE 语句而触发的触发器，请在 MERGE 操作分解到的 INSERT 和 UPDATE 语句上创建触发器。
DML 触发器可以是简单的，也可以是复合的。
一个简单的 DML 触发器恰好在以下时间点之一触发： - 在触发语句运行之前
（该触发器称为 BEFORE 语句触发器或语句级 BEFORE 触发器。）
-
触发语句运行后
（触发器称为 AFTER 语句触发器或语句级 AFTER 触发器。）
-
在触发语句影响的每一行之前
（该触发器称为 BEFORE 每行触发器或行级 BEFORE 触发器。）
-
在触发语句影响的每一行之后
（触发器称为 AFTER 每行触发器或行级 AFTER 触发器。）
在行级别触发的简单触发器可以访问它正在处理的行中的数据。有关详细信息，请参阅“相关名称和伪记录”。
INSTEAD OF DML触发器是在非编辑视图或非编辑视图的嵌套表列上创建的 DML 触发器 。除了在INSTEAD OF触发器中，触发UPDATE语句可以包含列列表。对于列列表，触发器仅在更新指定列时触发。如果没有列列表，则在更新关联表的任何列时触发触发器。
10.3.1. 检测触发DML语句的条件谓词
DML触发器的触发事件可以由多个触发语句组成。当其中一个触发触发器时，触发器可以通过使用这些条件谓词来确定哪一个。
| 条件谓词 | 条件 |
|---|---|
| INSERTING | 一个INSERT语句触发了触发器。 |
| UPDATING | 一个UPDATE语句触发了触发器。 |
| UPDATING | 影响指定列的UPDATE语句触发了触发器。 |
| DELETING | 一个DELETE语句触发了触发器。 |
条件谓词可以出现在BOOLEAN表达式可以出现的任何地方。
示例 10-1 触发器使用条件谓词检测触发语句
此示例创建一个 DML 触发器，该触发器使用条件谓词来确定四个可能的触发语句中的哪一个触发了它。
DROP TABLE IF EXISTS student;
CREATE TABLE student (id int PRIMARY KEY, name text, score number);
insert into student values (1, 'xx', 99);
insert into student values (1001, 'xs', 61);
insert into student values (1002, 'xd', 74);
insert into student values (1003, 'xc', 83);
insert into student values (1004, 'xg', 79);
insert into student values (1005, 'xl', 98);
\set SQLTERM /
CREATE OR REPLACE TRIGGER tri
BEFORE
INSERT OR
UPDATE OF score, id OR
DELETE
ON student
BEGIN
CASE
WHEN INSERTING THEN
RAISE NOTICE 'Inserting';
WHEN UPDATING('score') THEN
RAISE NOTICE 'Updating score';
WHEN UPDATING('department_id') THEN
RAISE NOTICE 'Updating department ID';
WHEN DELETING THEN
RAISE NOTICE 'Deleting';
END CASE;
END;
/
10.3.2. INSTEAD OF DML 触发器
INSTEAD OF DML 触发器是在非编辑视图或非编辑视图的嵌套表列上创建的DML 触发器。数据库触发INSTEAD OF触发器而不是运行触发 DML 语句。
INSTEAD OF触发器不能是有条件的。
INSTEAD OF触发器是更新本质上不可更新的视图的唯一方法。设计INSTEAD OF触发器以确定预期的操作并在基础表上执行适当的 DML 操作。
触发器始终是INSTEAD OF行级触发器。INSTEAD OF触发器可以读取OLD和NEW值，但不能更改它们。
仅当触发语句对视图的指定嵌套表列的元素进行操作时，才会触发带有 NESTED TABLE 子句的 INSTEAD OF 触发器。 触发器为每个修改的嵌套表元素触发。
另请参阅
-
《SQL语言参考手册》，了解有关固有可更新视图的信息
-
"复合 DML 触发器结构" INSTEAD OF EACH ROW 部分了解复合 DML 触发器的信息
示例 10-2 INSTEAD OF 触发器
此示例创建视图stu_info以显示有关学生的信息。视图本质上不是可更新的。该示例创建一个INSTEAD OF触发器来处理INSERT指向视图的语句。触发器将行插入到视图的基表student中.
CREATE OR REPLACE VIEW stu_info AS
SELECT id, name, score
FROM student
WHERE id > 1000;
\set SQLTERM /
CREATE OR REPLACE TRIGGER stu_info_insert
INSTEAD OF INSERT ON stu_info FOR EACH ROW
BEGIN
INSERT INTO student
VALUES (
:new.id,
:new.name,
:new.score);
END stu_info_insert;
/
查询以显示要插入的行不存在：
SELECT COUNT(*) FROM student WHERE id = 999;
结果：
count
-------
0
(1 row)
将行插入视图：
INSERT INTO stu_info VALUES (999, 'xy', 88);
结果：
1 row created.
查询以显示该行已插入student表中：
SELECT COUNT(*) FROM student WHERE id = 999;
结果：
Count（*）
----------
1
1 row selected.
10.3.3. 触发器在完整性检查的应用
您可以使用触发器和约束来维护父表和子表之间的引用完整性，如下表所示。（有关约束的更多信息，请参阅:ref:数据库 SQL 语言参考。）
| 表 | 在表上声明的约束 | 在表上创建的触发器 |
|---|---|---|
| Parent | PRIMARY KEY或者UNIQUE | 一个或多个触发器确保当PRIMARY KEY或 UNIQUE值被更新或删除时，所需的操作（RESTRICT、CASCADE 或 SET NULL）发生在相应的 FOREIGN KEY 值上。插入父表不需要任何操作，因为不存在依赖外键。 |
| Child | OREIGN KEY，如果父母和孩子在同一个数据库中。禁用此外键约束以防止删除相应的 PRIMARY KEY 或 UNIQUE 约束（明确使用 CASCADE 选项除外）。 | 一种触发器，可确保在 FOREIGN KEY 中插入或更新的值对应于父表中的 PRIMARY KEY 或 UNIQUE 值。 |
注意
以下主题中的示例使用这些共享列的表Deptno：
CREATE TABLE stu_score ( id INT NOT NULL, name VARCHAR2(10), score NUMBER); CREATE TABLE stu_class ( id INT NOT NULL, name VARCHAR2(10), class CHAR(1));
几个触发器包括锁定行的语句 ( SELECT FOR UPDATE)。此操作对于在处理行时保持并发性是必要的。
这些示例并不意味着完全按照书面形式使用。提供它们是为了帮助您设计自己的触发器。
10.3.3.1. 子表的外键触发
示例 10-3 中的触发器确保在 INSERT 或 UPDATE 语句影响外键值之前，相应的值存在于父键中。
示例 10-3 子表的外键触发器
insert into stu_score values (1, 'xx', 74);
insert into stu_score values (2, 'xl', 87);
insert into stu_score values (3, 'xs', 95);
insert into stu_class values (1, 'xx', 'C');
insert into stu_class values (2, 'xl', 'B');
insert into stu_class values (3, 'xs', 'A');
\set SQLTERM /
CREATE OR REPLACE TRIGGER score_class_check
BEFORE INSERT OR UPDATE OF id ON stu_score
FOR EACH ROW WHEN (NEW.id IS NOT NULL)
DECLARE
stu_id INTEGER; -- Use for cursor fetch
CURSOR stu_cursor (stu_id NUMBER) IS
SELECT id FROM stu_class
WHERE id = stu_id;
BEGIN
OPEN stu_cursor (:NEW.id);
FETCH stu_cursor INTO stu_id;
IF stu_cursor%NOTFOUND THEN
RAISE NOTICE 'NOT FOUND';
ELSE
RAISE NOTICE 'FOUND';
END IF;
CLOSE stu_cursor;
END;
/
10.3.3.2. 父表的UPDATE和DELETE RESTRICT触发器
示例 10-4 中的触发器对 stu_class 表的主键强制执行 UPDATE 和 DELETE RESTRICT 引用操作。
示例 10-4 父表的 UPDATE 和 DELETE RESTRICT 触发器
\set SQLTERM /
CREATE OR REPLACE TRIGGER class_restrict
BEFORE DELETE OR UPDATE OF id ON stu_class
FOR EACH ROW
-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.
DECLARE
stu_id INTEGER;
-- Cursor used to check for dependent foreign key values.
CURSOR stu_cursor (si NUMBER) IS
SELECT id FROM stu_score WHERE id = si;
BEGIN
OPEN stu_cursor (:OLD.id);
FETCH stu_cursor INTO stu_id;
CLOSE stu_cursor;
END;
10.3.3.3. 父表的 UPDATE 和 DELETE SET NULL 触发器
示例 10-5 中的触发器对 dept 表的主键强制执行 UPDATE 和 DELETE SET NULL 引用操作。
示例 10-5 父表的 UPDATE 和 DELETE SET NULL 触发器
\set SQLTERM /
CREATE OR REPLACE TRIGGER class_set_null
AFTER DELETE OR UPDATE OF id ON stu_class
FOR EACH ROW
BEGIN
IF UPDATING AND :OLD.id != :NEW.id OR DELETING THEN
UPDATE stu_score SET stu_score.id = NULL
WHERE stu_score.id = :OLD.id;
END IF;
END;
/
10.3.3.4. 父表的删除级联触发器
示例 10-6中的触发器DELETE CASCADE对表的主键强制执行引用操作。
示例 10-6 父表的 DELETE CASCADE 触发器
\set SQLTERM / CREATE OR REPLACE TRIGGER class_del_cascade AFTER DELETE ON stu_class FOR EACH ROW BEGIN DELETE FROM stu_score WHERE stu_score.id = :OLD.id; END; /
10.3.3.5. 父表的更新级联触发器
示例 10-7中的触发器确保如果表stu_class中的班级被更新，则此更改会传播到stu_score表中的相关外键。
注意
因为触发器 dept_cascade2 更新了 emp 表，所以示例 10-6 中的 emp_dept_check 触发器（如果启用）也会触发。 产生的变异表错误被 emp_dept_check 触发器捕获。 仔细测试任何需要错误捕获才能成功的触发器，以确保它们始终在您的环境中正常工作。
示例 10-7 父表的 UPDATE CASCADE 触发器
\set SQLTERM /
CREATE SEQUENCE update_seq
INCREMENT BY 1 MAXVALUE 5000 CYCLE;
CREATE OR REPLACE PACKAGE seq_pkg AUTHID DEFINER AS
Updateseq NUMBER;
END seq_pkg;
/
CREATE OR REPLACE PACKAGE BODY seq_pkg AS
END seq_pkg;
/
-- Create flag col:
ALTER TABLE stu_score ADD Update_id NUMBER;
CREATE OR REPLACE TRIGGER stu_class_cascade1
BEFORE UPDATE OF id ON stu_class
DECLARE
BEGIN
seq_pkg.Updateseq := update_seq.NEXTVAL;
END;
/
CREATE OR REPLACE TRIGGER stu_class_cascade2
AFTER DELETE OR UPDATE OF id ON stu_class
FOR EACH ROW
BEGIN
IF UPDATING THEN
UPDATE stu_score ss
SET ss.id = :NEW.id,
Update_id = seq_pkg.Updateseq --from 1st
WHERE id = :OLD.id
AND Update_id IS NULL;
/* Only NULL if not updated by 3rd trigger
fired by same triggering statement */
END IF;
IF DELETING THEN
DELETE FROM stu_score
WHERE id = :OLD.id;
END IF;
END;
/
CREATE OR REPLACE TRIGGER stu_class_cascade3
AFTER UPDATE OF id ON stu_class
BEGIN UPDATE stu_score ss
SET ss.Update_id = NULL
WHERE ss.Update_id = seq_pkg.Updateseq;
END;
/
10.3.3.6. 复杂约束检查的触发器
触发器可以强制执行引用完整性以外的完整性规则。:ref:`示例 10-8`中的触发器在允许触发语句运行之前进行了复杂的检查。
注意
:ref:`示例 10-8`需要这个数据结构：
CREATE TABLE scoregrade ( Loscore NUMBER, Hiscore NUMBER);
示例 10-8 触发器检查复杂约束
insert into scoregrade values (0, 100);
\set SQLTERM /
CREATE OR REPLACE TRIGGER score_check
BEFORE INSERT OR UPDATE OF score ON student
FOR EACH ROW
DECLARE
Minscore NUMBER;
Maxscore NUMBER;
BEGIN
SELECT Loscore, Hiscore INTO Minscore, Maxscore
FROM scoregrade;
IF (:NEW.score < Minscore OR :NEW.score > Maxscore) THEN
RAISE NOTICE 'score out of range';
END IF;
END;
/
10.3.3.7. 复杂安全授权的触发器
触发器通常用于对表数据强制执行复杂的安全授权。仅使用触发器来强制执行无法使用数据库提供的数据库安全功能定义的复杂安全授权。例如，使用触发器禁止student在周末和非工作时间更新表。
当使用触发器强制执行复杂的安全授权时，最好使用BEFORE语句触发器。使用BEFORE语句触发器有以下好处： - 安全检查是在触发语句被允许运行之前完成的，这样就不会因为未经授权的语句而浪费工作。 - 安全检查仅针对触发语句进行，而不针对受触发语句影响的每一行。
另请参阅
《数据库安全指南》了解有关数据库安全特性的详细信息
示例 10-9中的触发器通过student在周末或非工作时间 尝试更新表时引发异常来强制执行安全性。
示例 10-9 触发器强制执行安全授权
\set SQLTERM /
CREATE OR REPLACE TRIGGER student_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON student
DECLARE
stu_id INTEGER;
out_weekends EXCEPTION;
outworking_hours EXCEPTION;
PRAGMA EXCEPTION_INIT (out_weekends, -4097);
PRAGMA EXCEPTION_INIT (outworking_hours, -4099);
BEGIN
-- Check for weekends:
IF (TO_CHAR(Sysdate, 'DAY') = 'SAT' OR
TO_CHAR(Sysdate, 'DAY') = 'SUN') THEN
RAISE out_weekends;
END IF;
-- Check for work hours (8am to 6pm):
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE outworking_hours;
END IF;
EXCEPTION
WHEN out_weekends THEN
Raise_application_error(-20324,'Might not change '
||'student table during the weekend');
WHEN outworking_hours THEN
Raise_application_error(-20326,'Might not change '
||'emp table during Nonworking hours');
END;
/
10.3.4. 透明事件记录的触发器
当您希望在某些事件之后透明地在数据库中进行相关更改时，触发器非常有用。
触发器示例显示了一个触发器，该REORDER触发器在满足某些条件时根据需要对部件进行重新排序。（也就是说，输入了一条触发语句，并且PARTS_ON_HAND值小于该REORDER_POINT值。）
10.3.5. 导出列值的触发器
触发器可以根据 INSERT 或 UPDATE 语句提供的值自动派生列值。 这种类型的触发器对于强制特定列中的值依赖于同一行中其他列的值很有用。 BEFORE 行触发器是完成此类操作所必需的，原因如下： - 依赖值必须在INSERT或UPDATE发生之前派生，以便触发语句可以使用派生值。 - 触发器必须为受触发INSERT或UPDATE语句影响的每一行触发。
每当插入或更新行时 ，示例 10-10中的触发器都会为表派生新的列值。
注意
示例 10-10需要对这个数据结构进行以下更改：
ALTER TABLE stu_score ADD( Uppername VARCHAR2(20));
示例 10-10 触发器派生新列值
\set SQLTERM / CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF name ON stu_score FOR EACH ROW BEGIN :NEW.Uppername := UPPER(:NEW.name); END; /
10.4. 相关名称和伪记录
注意
本主题仅适用于行级简单 DML 触发器。
在行级别触发的触发器可以使用相关名称访问正在处理的行中的数据。默认的相关名称是OLD、NEW。要更改相关名称，请使用CREATE TRIGGER语句的REFERENCING子句（请参阅“ referencing_clause ::= ”）。
如果触发器是在嵌套表上创建的，则OLD和NEW引用嵌套表的当前行。如果触发器是在表或视图上创建的，则OLD并NEW引用表或视图的当前行，并且PARENT是未定义的。
如果触发器是在嵌套表上创建的，那么 OLD 和 NEW 指的是嵌套表的当前行。 如果触发器是在表或视图上创建的，则 OLD 和 NEW 指的是表或视图的当前行。
OLD、NEW也称为伪记录，因为它们具有记录结构，但允许在比记录更少的上下文中使用。 伪记录的结构是 table_name%ROWTYPE，其中 table_name 是创建触发器的表的名称（对于 OLD 和 NEW）。
在简单触发器的 trigger_body 中，相关名称是绑定变量的占位符。 使用以下语法引用伪记录的字段：
:pseudorecord_name.field_name
在WHEN条件触发器的子句中，相关名称不是绑定变量的占位符。因此，请省略上述语法中的冒号。
下表显示了触发语句正在处理的行的 OLD 和 NEW 字段的值。
| 触发语句 | OLD.field值 | NEW.field 值 |
|---|---|---|
| INSERT | NULL | 插入后值 |
| UPDATE | 更新前值 | 更新后值 |
| DELETE | 预删除值 | NULL |
对伪记录的限制是： - 伪记录不能是实际的子程序参数。
（伪记录字段可以是实际的子程序参数。）
-
触发器不能更改OLD字段值。
试图这样做可能会引发异常。
-
如果触发语句为DELETE，则触发器无法更改NEW字段值。
试图这样做可能会引发异常。
-
AFTER触发器不能更改NEW字段值，因为触发语句在触发器触发之前运行。
试图这样做会引发异常。
BEFORE 触发器可以在触发 INSERT 或 UPDATE 语句将新字段值放入表中之前更改它们。
如果一个语句同时触发了 BEFORE 触发器和 AFTER 触发器，并且 BEFORE 触发器更改了 NEW 字段值，那么 AFTER 触发器能看到该更改。
示例 10-11 触发器记录对 stu_score.score 的更改
UPDATE此示例创建一个日志表和一个触发器，该触发器在任何语句影响表的score列之后在日志表中插入一行student，然后更新student.score并显示日志表。
创建日志表：
CREATE TABLE stu_log ( id NUMBER, log_date DATE, new_score NUMBER, action varchar2(20));
创建在 stu_score.score 更新后在日志表中插入行的触发器：
\set SQLTERM / CREATE OR REPLACE TRIGGER log_score_increase AFTER UPDATE OF score ON stu_score FOR EACH ROW BEGIN INSERT INTO stu_log (id, log_date, new_score, action) VALUES (:NEW.id, SYSDATE, :NEW.score, 'new score'); END; /
更新 stu_score.score：
UPDATE stu_score SET score = score - 5.0 WHERE id = 1;
结果：
UPDATE 2
显示日志表：
SELECT * FROM stu_log;
结果：
id | log_date | new_score | action ----+---------------------+-----------+----------- 1 | 2012-06-22 17:04:40 | 79.0 | new score 1 | 2012-06-22 17:04:40 | 82.0 | new score (2 rows)
示例 10-12 条件触发器打印得分变化信息
此示例创建了一个条件触发器，只要 DELETE、INSERT 或 UPDATE 语句影响 student 表，该触发器就会打印工资变化信息，除非该信息是关于总裁的。 数据库评估每个受影响行的 WHEN 条件。 如果受影响行的 WHEN 条件为 TRUE，则触发器会在触发语句运行之前针对该行触发。 如果受影响行的 WHEN 条件不为 TRUE，则触发器不会针对该行触发，但触发语句仍会运行。
\set SQLTERM / CREATE OR REPLACE TRIGGER show_score_change BEFORE INSERT OR UPDATE ON stu_score FOR EACH ROW WHEN (NEW.score > 80) -- do not print information about President DECLARE score_diff NUMBER; BEGIN score_diff := :NEW.score - :OLD.score; RAISE NOTICE 'new.name = %,old.score = %,new.score = %',NEW.name, OLD.score, NEW.score; RAISE NOTICE 'Difference: %' ,score_diff; END; /
查询：
SELECT id, name, score FROM stu_score ORDER BY id, name;
结果：
id | name | score ----+------+------- 1 | xr | 82.0 1 | xx | 79.0 2 | xl | 87 3 | xs | 95 (4 rows)
触发语句：
UPDATE stu_score SET score = score * 1.05;
结果：
NOTICE: new.name = xl,old.score = 87,new.score = 91.35 NOTICE: Difference: 4.35 NOTICE: new.name = xs,old.score = 95,new.score = 99.75 NOTICE: Difference: 4.75 NOTICE: new.name = xx,old.score = 79.0,new.score = 82.950 NOTICE: Difference: 3.950 NOTICE: new.name = xr,old.score = 82.0,new.score = 86.100 NOTICE: Difference: 4.100 UPDATE 4
查询：
SELECT id, name, score FROM stu_score ORDER BY id, name;
结果：
id | name | score ----+------+-------- 1 | xr | 86.100 1 | xx | 82.950 2 | xl | 91.35 3 | xs | 99.75 (4 rows)
示例 10-13 触发器修改 CLOB 列
此示例创建一个UPDATE修改CLOB列的触发器。
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 CLOB);
INSERT INTO t1 VALUES ('<h1>HTML TEXT</h1><p>Some text.');
\set SQLTERM /
CREATE OR REPLACE TRIGGER tri
BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
RAISE NOTICE 'Old value of CLOB column: %', :OLD.c1;
RAISE NOTICE 'Proposed new value of CLOB column: %',:NEW.c1;
:NEW.c1 := :NEW.c1 || '<hr><p>Standard footer paragraph.';
RAISE NOTICE 'Final value of CLOB column: %',:NEW.c1;
END;
/
UPDATE t1 SET c1 = '<h1>Different Document Fragment</h1><p>Different text.';
SELECT * FROM t1;
结果：
NOTICE: Old value of CLOB column: <h1>HTML TEXT</h1><p>Some text. NOTICE: Proposed new value of CLOB column: <h1>Different Document Fragment</h1><p>Different text. NOTICE: Final value of CLOB column: <h1>Different Document Fragment</h1><p>Different text.<hr><p>Standard footer paragraph. UPDATE 1
示例 10-14 带有 REFERENCING 子句的触发器
此示例创建一个与相关名称同名的表，new然后在该表上创建触发器。为了避免表名和相关名之间的冲突，触发器将相关名引用为Newest。
CREATE TABLE new ( f1 NUMBER, f2 VARCHAR2(20) ); \set SQLTERM / CREATE OR REPLACE TRIGGER show_score_change BEFORE UPDATE ON new REFERENCING new AS Newest FOR EACH ROW BEGIN :Newest.f2 := TO_CHAR (:newest.f1); END; /
10.5. 事件触发器
其触发事件由 DDL 语句（列于“ ddl_event ”）组成。
详情参见:ref:触发器描述。
10.6. 触发器调用的子程序
触发器可以调用用 PL/SQL、C 和 Java 编写的子程序。例 10-4中的触发器调用了一个 PL/SQL 子程序。触发器调用的子程序不能运行事务控制语句，因为子程序运行在触发器体的上下文中。
如果触发器调用调用者权限 (IR) 子程序，则创建触发器的用户（而不是运行触发语句的用户）被视为当前用户。有关 IR 子程序的信息，请参阅“调用者的权限和定义者的权利（AUTHID 属性） ”。
如果触发器调用远程子程序，并且在触发器执行期间发现时间戳或签名不匹配，则远程子程序不运行并且触发器无效。
10.7. 触发编译、失效和重新编译
该CREATE TRIGGER语句编译触发器并将其代码存储在数据库中。如果发生编译错误，仍然会创建触发器，但其触发语句会失败，但以下情况除外： - 触发器是在禁用状态下创建的。
如果触发器引用另一个对象，例如子程序或包，并且该对象被修改或删除，则触发器将变为无效。下次触发事件发生时，编译器会尝试重新验证触发器。
要手动重新编译触发器，请使用“ ALTER TRIGGER 语句”:ref:`ALTER TRIGGER`中描述的语句。
10.8. 触发器中的异常处理
在大多数情况下，如果触发器运行引发异常的语句，并且异常未由异常处理程序处理，则数据库将回滚触发器及其触发语句的影响。
注意
强制执行复杂的安全授权或约束的触发器通常会引发用户定义的异常。这些在"用户定义的异常"
另请参阅
"PL/SQL 错误处理"，关于异常处理的一般信息
远程异常处理
只有当远程数据库可用时，访问远程数据库的触发器才能进行远程异常处理。如果本地数据库必须编译触发器时远程数据库不可用，则本地数据库无法验证访问远程数据库的语句，编译失败。如果触发器无法编译，则其异常处理程序无法运行。
:ref:`示例 10-15`中的触发器有一个INSERT访问远程数据库的语句。触发器还有一个异常处理程序。但是，如果本地数据库尝试编译触发器时远程数据库不可用，则编译失败并且异常处理程序无法运行。
:ref:`示例 10-16`显示了:ref:`示例 10-16`中问题的解决方法：将远程INSERT语句和异常处理程序放入存储的子程序中，并让触发器调用存储的子程序。子程序以编译的形式存储在本地数据库中，并带有用于访问远程数据库的经过验证的语句。因此，当远程INSERT语句由于远程数据库不可用而失败时，子程序中的异常处理程序可以处理它。
示例 10-15 如果远程数据库不可用，触发器无法处理异常
\set SQLTERM /
CREATE OR REPLACE PROCEDURE insert_row_proc AUTHID CURRENT_USER AS
no_remote_db EXCEPTION; -- declare exception
PRAGMA EXCEPTION_INIT (no_remote_db, -20000);
BEGIN
INSERT INTO student@remote (
student_id, first_name, last_name, email, hire_date, job_id
)
VALUES (
99, 'Jane', 'Doe', 'jane.doe@example.com', SYSDATE, 'ST_MAN'
);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO stu_log (id, log_date, new_score, action)
VALUES (99, SYSDATE, NULL, 'insert failed.');
RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.');
END;
/
CREATE OR REPLACE TRIGGER student_tr
AFTER INSERT ON student
FOR EACH ROW
BEGIN
insert_row_proc;
END;
/
10.9. 触发器设计指南
-
使用触发器确保无论何时发生特定事件，都会执行任何必要的操作（无论哪个用户或应用程序发出触发语句）。
例如，使用触发器确保每当有人更新表时，都会更新其日志文件。
-
不要创建重复数据库功能的触发器。
例如，如果您可以对约束执行相同操作，请不要创建触发器来拒绝无效数据。
-
不要创建依赖于 SQL 语句处理行的顺序（可能会有所不同）的触发器。
例如，如果变量的当前值取决于行触发器正在处理的行，则不要将值分配给行触发器中的全局包变量。如果触发器更新全局包变量，请在BEFORE语句触发器中初始化这些变量。
-
BEFORE在将行数据写入磁盘之前，使用行触发器修改行。
-
使用AFTER行触发器获取行 ID 并在操作中使用它。
注意
AFTER行触发器比BEFORE行触发器更有效。使用BEFORE行触发器，首先为触发器读取受影响的数据块，然后为触发语句读取。对于AFTER行触发器，受影响的数据块对于触发器是只读的。
-
如果 BEFORE 语句触发器的触发语句是与正在运行的 UPDATE 语句冲突的 UPDATE 或 DELETE 语句，则数据库对 SAVEPOINT 执行透明 ROLLBACK 并重新启动触发语句。 在触发语句成功完成之前，数据库可以多次执行此操作。 每次数据库重新启动触发语句时，触发器都会触发。 ROLLBACK 到 SAVEPOINT 不会撤消对触发器引用的包变量的更改。 要检测这种情况，请在包中包含一个计数器变量。
-
不要创建递归触发器。
例如，不要创建在定义触发器的表上AFTER UPDATE发出语句的触发器。UPDATE触发器递归触发，直到内存不足。
-
如果您创建的触发器包含访问远程数据库的语句，则将该语句的异常处理程序放在存储的子程序中并从触发器调用子程序。
有关详细信息，请参阅“远程异常处理”。
-
如果触发器运行以下语句，则该语句返回触发器的所有者，而不是更新表的用户：
-
只有提交的触发器才会触发。
-
要允许模块化安装在相同表上具有触发器的应用程序，请创建多个相同类型的触发器，而不是运行一系列操作的单个触发器。
每个触发器都会看到先前触发的触发器所做的更改。每个触发器都可以看到OLD和NEW值。
10.10. 触发器触发的顺序
如果为同一张表上的同一语句定义了 两个或多个具有不同时间点的触发器，则它们按以下顺序触发：
-
所有BEFORE STATEMENT触发器
-
所有BEFORE EACH ROW触发器
-
所有AFTER EACH ROW触发器
-
所有AFTER STATEMENT触发器
10.11. 触发器启用和禁用
默认情况下，CREATE TRIGGER语句创建一个处于启用状态的触发器。要在禁用状态下创建触发器，请指定DISABLE。在禁用状态下创建触发器可以确保它在启用之前编译没有错误。
暂时禁用触发器的一些原因是： - 触发器引用了一个不可用的对象。 - 您必须进行大量数据加载，并且希望它在不触发触发器的情况下快速进行。 - 您正在重新加载数据。
要启用或禁用单个触发器，请使用以下语句：
ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
要启用或禁用在特定表上创建的所有版本中的所有触发器，请使用以下语句：
ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
在前面的两个语句中，schema是包含触发器的模式的名称，默认是您的模式。
另请参阅
-
“ALTER TRIGGER 语句”以获取有关该 ALTER TRIGGER 语句的更多信息
-
:ref:`SQL 语言参考`以获取有关该 ALTER TABLE 语句
10.12. 触发器更改和调试
要更改触发器，您必须替换或重新创建它。（该ALTER TRIGGER语句仅启用、禁用、编译或重命名触发器。）
要替换触发器，请使用CREATE TRIGGER带有OR REPLACE子句的语句。
要重新创建触发器，请先将其与DROP TRIGGER语句一起删除，然后再使用CREATE TRIGGER语句再次创建它。
要调试触发器，您可以使用可用于存储子程序的工具。有关这些工具的信息，请参阅:ref:数据库开发指南
另请参阅
-
“ CREATE TRIGGER 语句”以获取有关该CREATE TRIGGER语句的更多信息
-
“ DROP TRIGGER 语句”以获取有关该DROP TRIGGER语句的更多信息
-
“ ALTER TRIGGER 语句”以获取有关该ALTER TRIGGER语句的更多信息
10.13. 触发器和KES数据库数据传输实用程序
将数据传输到数据库（可能触发触发器）的 KES 数据库实用程序是： - 数据泵导入 (impdp)
数据泵导入 (impdp) 读取由数据泵导出 (expdp) 创建的导出转储文件集，并将其写入数据库。
-
原始导入 (imp)
原始导入（原始导入实用程序，imp）从原始导出（原始导出实用程序，exp）创建的转储文件中读取对象定义和表数据，并将它们写入目标数据库。
注意
要导入原始导出创建的文件，您必须使用原始导入。在所有其他情况下，建议您使用数据泵导入而不是原始导入。
如果要导入的表在目标数据库中不存在，则 imp 在创建任何触发器之前创建并加载该表，因此不会触发任何触发器。
如果目标数据库上存在要导入的表，则 Import IGNORE 参数确定触发器是否在导入操作期间触发。 IGNORE 参数指定是否忽略对象创建错误，从而导致以下行为： - 如果IGNORE=n（默认），imp则不更改表并且不触发触发器。 - 如果IGNORE=y, 则将imp行加载到现有表中，并INSERT触发在表上创建的触发器。
10.14. 触发器信息视图
*_TRIGGERS静态数据字典视图显示有关触发器的信息。有关这些视图的信息，请参阅:ref:数据库参考。
示例 10-16 查看有关触发器的信息
此示例创建一个触发器并查询静态数据字典视图USER_TRIGGERS两次——首先显示其类型、触发事件和创建它的表的名称，然后显示其主体。
\set SQLTERM / CREATE OR REPLACE TRIGGER Stu_count AFTER DELETE ON student DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM student; RAISE NOTICE 'There are now % student.', n; END; /
查询：
SELECT Trigger_type, Triggering_event, Table_name FROM USER_TRIGGERS WHERE Trigger_name = 'STU_COUNT';
结果：
trigger_type | triggering_event | table_name -----------------+------------------+------------ AFTER STATEMENT | DELETE | student (1 row)
查询：
SELECT Trigger_body FROM USER_TRIGGERS WHERE Trigger_name = 'EMP_COUNT';
结果：
trigger_body ---------------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER emp_count AFTER DELETE ON public.student FOR EACH STATEMENT + DECLARE + n INTEGER; + BEGIN + SELECT COUNT(*) INTO n FROM student; + RAISE NOTICE 'There are now % student.', n; + END (1 row)
347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
