Oracle PL/SQL 触发器教程：复合触发器 [示例]

PL/SQL 中的触发器是什么？

触发器 是被触发的存储程序 Oracle 当在表上执行插入、更新、删除等 DML 语句或发生某些事件时，引擎会自动触发。可以根据需要定义在触发器情况下要执行的代码。您可以选择需要触发触发器的事件以及执行的时间。触发器的目的是维护数据库上信息的完整性。

触发器的好处

以下是触发器的好处。

• 自动生成一些派生列值
• 强制引用完整性
• 事件记录和存储表访问信息
• 审计
• Sync表的同步复制
• 实施安全授权
• 防止无效交易

触发器的类型 Oracle

触发器可以根据以下参数进行分类。

• 分类依据 定时
• 前触发器：它在指定事件发生之前触发。
• AFTER 触发器：在指定事件发生后触发。
• INSTEAD OF 触发器：一种特殊类型。您将了解有关后续主题的更多信息。（仅适用于 DML）
• 分类依据 水平
• STATEMENT级触发器：它针对指定的事件语句触发一次。
• ROW 级别触发器：它会针对指定事件中受影响的每条记录触发。（仅适用于 DML）
• 分类依据 创建
• DML 触发器：当指定 DML 事件（INSERT/UPDATE/DELETE）时触发
• DDL 触发器：当指定 DDL 事件（CREATE/ALTER）时触发
• 数据库触发器：当指定数据库事件（LOGON/LOGOFF/STARTUP/SHUTDOWN）时触发

因此每个触发器都是上述参数的组合。

如何创建触发器

以下是创建触发器的语法。

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

语法解释：

• 上述语法显示了触发器创建中存在的不同可选语句。
• 之前/之后将指定事件时间。
• INSERT/UPDATE/LOGON/CREATE/等将指定需要触发触发器的事件。
• ON 子句将指定上述事件在哪个对象上有效。例如，在 DML 触发器的情况下，这将是可能发生 DML 事件的表名。
• 命令“FOR EACH ROW”将指定ROW级别触发器。
• WHEN 子句将指定触发器需要触发的附加条件。
• 声明部分，执行部分，异常处理部分与其他的相同 PL/SQL 块. 声明部分和异常处理部分是可选的。

:NEW 和 :OLD 子句

在行级触发器中，触发器针对每个相关行触发。有时需要知道 DML 语句之前和之后的值。

Oracle 在 RECORD 级触发器中提供了两个子句来保存这些值。我们可以使用这些子句在触发器主体内部引用旧值和新值。

• :NEW – 在触发器执行期间，它为基表/视图的列保存新值
• :OLD – 它在触发器执行期间保存基表/视图的列的旧值

此子句应根据 DML 事件使用。下表将指定哪个子句对哪个 DML 语句（INSERT/UPDATE/DELETE）有效。

	插入	更新	删除
：新的	有效	有效	无效。删除案例中没有新值。
：老的	无效。插入案例中没有旧值	有效	有效

代替触发器

“INSTEAD OF 触发器”是一种特殊类型的触发器。它仅用于 DML 触发器。当复杂视图上将发生任何 DML 事件时使用它。

考虑一个视图由 3 个基表构成的示例。当在此视图上发出任何 DML 事件时，该事件将变得无效，因为数据取自 3 个不同的表。因此在此使用 INSTEAD OF 触发器。INSTEAD OF 触发器用于直接修改基表，而不是修改给定事件的视图。

例子1：在这个例子中，我们将从两个基表创建一个复杂的视图。

• Table_1 是 emp 表，

• Table_2是部门表。

然后我们将了解如何使用 INSTEAD OF 触发器来发出此复杂视图上的 UPDATE 位置详细信息语句。我们还将了解 :NEW 和 :OLD 在触发器中的用途。

• 步骤 1：创建具有适当列的表“emp”和“dept”
• 步骤 2：使用示例值填充表格
• 步骤 3：为上面创建的表创建视图
• 步骤 4：在替换触发器之前更新视图
• 步骤 5：创建代替触发器
• 步骤 6：在 Instead-of 触发后更新视图

步骤1） 创建具有适当列的表“emp”和“dept”

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept( 
Dept_no NUMBER, 
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

代码说明

• 代码行 1-7：创建表‘emp’。
• 代码行 8-12：表‘dept’创建。

输出

表已创建

步骤2） 现在，由于我们已经创建了表，我们将用示例值填充该表，并为上述表创建视图。

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

代码说明

• 代码行 13-19：将数据插入“dept”表。
• 代码行20-26： 将数据插入“emp”表。

输出

PL/SQL 过程 完成

步骤3） 为上面创建的表创建一个视图。

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

代码说明

• 代码行27-32： 创建“guru99_emp_view”视图。

• 代码第 33 行： 查询 guru99_emp_view。

输出

查看已创建

员工姓名	部门名称	LOCATION
ZZZ	HR	美国
YYY	联系销售	UK
XXX	财务	日本

步骤4） 在 Instead-of 触发器之前更新视图。

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;
COMMIT;
END;
/

代码说明

• 代码行34-38： 将“XXX”的位置更新为“FRANCE”。它引发异常，因为 DML 语句 在复杂视图中是不允许的。

输出

ORA-01779: 无法修改映射到非键保留表的列

ORA-06512: 在第 2 行

步骤5）为了避免上一步更新视图时遇到错误，这一步我们将使用“代替触发器”。

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

代码说明

• 代码第 39 行： 在 ROW 级别的“guru99_emp_view”视图上为“UPDATE”事件创建 INSTEAD OF 触发器。它包含更新语句以更新基表“dept”中的位置。
• 代码第 44 行： 更新语句使用':NEW'和':OLD'来查找更新前后列的值。

输出

触发器已创建

步骤6） 替代触发器之后的视图更新。现在不会出现错误，因为“替代触发器”将处理此复杂视图的更新操作。并且当代码执行时，员工 XXX 的位置将从“日本”更新为“法国”。

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

代码说明：

• 代码行49-53： 将“XXX”的位置更新为“FRANCE”。此操作成功，因为“INSTEAD OF”触发器已停止对视图的实际更新语句并执行了基表更新。
• 代码第 55 行： 验证更新的记录。

输出：

PL/SQL 过程已成功完成

员工姓名	部门名称	LOCATION
ZZZ	HR	美国
YYY	联系销售	UK
XXX	财务	法国

复合扳機

复合触发器是一种允许您在单个触发器主体中为四个时间点分别指定操作的触发器。它支持的四个不同时间点如下。

• 声明前 – 级别
• 前行 – 水平
• 后排 – 水平
• 声明后 – 级别

它提供了将不同时间的动作组合到同一触发器中的功能。

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

语法解释：

• 上述语法显示“COMPOUND”触发器的创建。
• 声明部分对于触发器主体中的所有执行块都是通用的。
• 这 4 个计时块可以按任意顺序排列。不一定要有全部 4 个计时块。我们可以只为需要的计时块创建复合触发器。

例子1：在此示例中，我们将创建一个触发器，使用默认值 5000 自动填充工资列。

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

代码说明：

• 代码行 2-10：创建复合触发器。创建该触发器是为了在行级之前计时，以默认值 5000 填充工资。这会在将记录插入表之前将工资更改为默认值“5000”。
• 代码行 11-14：将记录插入到“emp”表中。
• 代码行 16：验证插入的记录。

输出：

已创建触发器

PL/SQL 过程已成功完成。

EMP_NAME	雇员编号	薪金	经理	部门编号
CCC	1004	5000	AAA	30

启用和禁用触发器

触发器可以启用或禁用。要启用或禁用触发器，需要为触发器提供禁用或启用它的 ALTER（DDL）语句。

以下是启用/禁用触发器的语法。

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

语法解释：

• 第一个语法显示如何启用/禁用单个触发器。
• 第二条语句显示如何启用/禁用特定表上的所有触发器。

结语

在本章中，我们了解了 PL/SQL 触发器及其优点。我们还了解了不同的分类，并讨论了 INSTEAD OF 触发器和 COMPOUND 触发器。