Oracle PL/SQL 触发器：代替 & 复合类型

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

触发器已存储 PL / SQL 由以下因素触发的程序 Oracle 发动机自动启动 DML 语句 例如，当对表执行插入、更新和删除操作，或发生某些事件时，触发器就会执行相应的操作。触发器执行的代码可以根据需要进行定义。您可以选择触发触发器的事件以及执行时间。触发器的目的是维护数据库中信息的完整性。

触发器的好处

以下是触发器的好处。

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

触发器的类型 Oracle

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

基于时间的分类

• 触发前： 它会在指定事件发生之前触发。
• 触发后： 它会在指定事件发生后触发。
• 代替触发器： 一种特殊类型。您将在后续主题中了解更多信息。（仅适用于 DML）

基于级别的分类

• 语句级触发器： 它会针对指定的事件语句触发一次。
• 行级别触发器： 它会针对指定事件中受影响的每个记录触发。（仅适用于 DML 操作）

基于事件的分类

• DML触发器： 当指定 DML 事件（INSERT/UPDATE/DELETE）时，它会触发。
• DDL触发器： 当指定 DDL 事件（CREATE/ALTER）时，它会触发。
• 数据库触发器： 当指定数据库事件（LOGON/LOGOFF/STARTUP/SHUTDOWN）时触发。

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

如何创建触发器

以下是创建触发器的语法。下面的屏幕截图显示了此触发器创建语法。 Oracle.

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;

语法解释：

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

:NEW 和 :OLD 子句

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

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

• ：新的 – 它在触发器执行期间保存基础表/视图列的新值。
• ：老的 – 它保存触发器执行期间基表/视图列的旧值。

此子句应根据 DML 事件使用。下表列出了每个 DML 语句（INSERT/UPDATE/DELETE）对应的有效子句。

代替触发器

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

假设有一个视图由三个基表构成。当对该视图执行任何 DML 事件时，由于数据来自三个不同的表，该视图将失效。因此，在这种情况下，需要使用 INSTEAD OF 触发器。INSTEAD OF 触发器用于在给定事件发生时直接修改基表，而不是修改视图。

例如1： 在这个例子中，我们将从两个基本表创建一个复杂的视图，其中 Table_1 是员工表，Table_2 是部门表。

接下来，我们将了解如何使用 INSTEAD OF 触发器来更新此复杂视图中的位置详细信息。我们还将了解 :NEW 和 :OLD 在触发器中的用途。示例按以下步骤完成：

• 步骤 1：创建包含适当列的表“emp”和“dept”
• 步骤 2：用样本值填充表格
• 步骤 3：为上述创建的表创建视图
• 步骤 4：在 INSTEAD OF 触发器之前更新视图
• 步骤 5：创建 INSTEAD OF 触发器
• 步骤 6：INSTEAD OF 触发器后的视图更新

步骤 1）创建表“emp”和“dept”，并添加适当的列。

下面的屏幕截图显示了“emp”和“dept”基本表的创建过程。 Oracle.

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));
/

Code 说明

• Code 第 1-7 行： 创建表“emp”。
• Code 第 8-12 行： 创建“部门”表。

输出：

Table Created

步骤2） 现在，既然我们已经创建了表格，我们将用示例值填充它们。

下面截图显示了将示例行插入到“dept”和“emp”表中的情况。

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code 说明

• Code 第 13-19 行： 将数据插入“dept”表。
• Code 第 20-26 行： 向“emp”表中插入数据。

输出：

PL/SQL procedure completed

步骤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;

Code 说明

• Code 第 27-32 行： 创建“guru99_emp_view”视图。
• Code 第33行： 查询 guru99_emp_view。

输出：

View created

步骤4） 在 INSTEAD OF 触发器之前更新视图。

下面的屏幕截图显示了对复杂视图的更新尝试以及由此产生的错误。

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

Code 说明

• Code 第 34-38 行： 将“XXX”的位置更新为“FRANCE”。由于不允许直接在复杂视图上执行 DML 语句，因此引发了异常。

输出：

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

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

下面的屏幕截图显示了 INSTEAD OF 触发器的创建。

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;
/

Code 说明

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

输出：

Trigger Created

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

下面的屏幕截图显示了通过 INSTEAD OF 触发器成功更新以及刷新后的视图。

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

SELECT * FROM guru99_emp_view;

Code 说明：

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

输出：

PL/SQL procedure successfully completed

复合扳機

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

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

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

下面的屏幕截图显示了复合触发器语法及其四个计时部分。

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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;

语法解释：

• 以上语法展示了如何创建“复合”触发器。
• 声明部分对于触发器主体中的所有执行块都是通用的。
• 这四个时序块可以按任意顺序排列，并非必须包含全部四个时序块。我们可以仅为所需的时序创建复合触发器。

例如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;

Code 说明：

• Code 第 2-10 行： 创建复合触发器。该触发器创建于行级别之前，用于将薪资填充为默认值 5000。这将在将记录插入表之前，将薪资更改为默认值“5000”。
• Code 第 11-14 行： 将记录插入到“emp”表中。
• Code 第16行： 正在验证插入的记录。

输出：

Trigger created

PL/SQL procedure successfully completed.

启用和禁用触发器

触发器可以启用或禁用。要启用或禁用触发器，需要为该触发器编写一条 ALTER (DDL) 语句来启用或禁用它。

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

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

语法解释：

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

常见问题