Oracle PL/SQL 触发器教程：Instead of、Compound [示例]

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

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

触发器的优点

以下是触发器的优点。

自动生成某些派生列值
强制执行参照完整性
事件日志记录和存储表访问信息
审计
表的同步复制
强制执行安全授权
防止无效事务

Oracle 中的触发器类型

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

基于时间的分类
BEFORE 触发器：在指定事件发生之前触发。
AFTER 触发器：在指定事件发生之后触发。
INSTEAD OF 触发器：特殊类型。您将在后面的主题中了解更多。 (仅用于 DML)
基于级别的分类
语句级触发器：为指定的事件语句触发一次。
行级触发器：为指定事件中受影响的每条记录触发一次。 (仅用于 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;

语法说明

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

:NEW 和 :OLD 子句

在行级触发器中，触发器会为每行相关记录触发。有时需要了解 DML 语句之前和之后的值。

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

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

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

	INSERT	UPDATE	DELETE
:NEW	有效	有效	无效。删除情况没有新值。
:OLD	无效。插入情况没有旧值	有效	有效

INSTEAD OF 触发器

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

例如，一个视图由 3 个基表组成。当对该视图发出任何 DML 事件时，它将变得无效，因为数据来自 3 个不同的表。因此，这里使用了 INSTEAD OF 触发器。 INSTEAD OF 触发器用于直接修改基表，而不是修改给定事件的视图。

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

Table_1 是 emp 表，

Table_2 是 department 表。

然后我们将看到 INSTEAD OF 触发器如何用于对该复杂视图发出 UPDATE 位置详细信息语句。我们还将看到 :NEW 和 :OLD 在触发器中的用处。

步骤 1：创建具有适当列的表“emp”和“dept”
步骤 2：用示例值填充表
步骤 3：为上述创建的表创建视图
步骤 4：在 instead-of 触发器之前更新视图
步骤 5：创建 instead-of 触发器
步骤 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。

输出

视图已创建

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	美国
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

步骤 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)为了避免上一步更新视图时遇到的错误，在此步骤我们将使用“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;
/

代码解释

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

输出

触发器已创建

步骤 6) 在 instead-of 触发器之后更新视图。现在不会出现错误，因为“instead of 触发器”将处理此复杂视图的更新操作。并且在代码执行时，员工 XXX 的位置将从“Japan”更新为“France”。

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 过程成功完成

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	美国
YYY	SALES	UK
XXX	FINANCIAL	FRANCE

复合触发器

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

BEFORE STATEMENT – 级别
BEFORE ROW – 级别
AFTER ROW – 级别
AFTER STATEMENT – 级别

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

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 个时间块。我们可以仅为所需的时序创建 COMPOUND 触发器。

示例 1：在此示例中，我们将创建一个触发器来自动填充 salary 列，默认值为 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：创建复合触发器。它是在 BEFORE ROW 级别的时间点创建的，用于将 salary 填充为默认值 5000。这将在记录插入表之前将 salary 更改为默认值 '5000'。
代码行 11-14：将记录插入 'emp' 表。
代码行 16：验证插入的记录。

输出

触发器已创建

PL/SQL 过程成功完成。

EMP_NAME	EMP_NO	SALARY	MANAGER	DEPT_NO
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 触发器。

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

触发器的优点

Oracle 中的触发器类型

如何创建触发器

相关文章

:NEW 和 :OLD 子句

INSTEAD OF 触发器

复合触发器

摘要

订阅新闻通讯