PL/SQL 触发器

触发器由 Oracle 引擎在指定的事件发生时自动调用。触发器存储在数据库中，并在特定条件匹配时重复调用。

触发器是存储的程序，当某些事件发生时，会自动执行或触发。

编写触发器是为了响应以下任何事件而执行。

• 数据库操作 (DML) 语句（DELETE、INSERT 或 UPDATE）。
• 数据库定义 (DDL) 语句（CREATE、ALTER 或 DROP）。
• 数据库操作（SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN）。

可以在与事件关联的表、视图、模式或数据库上定义触发器。

触发器的优点

以下是触发器的优点

• 触发器自动生成一些派生的列值
• 强制参照完整性
• 事件日志记录和存储有关表访问的信息
• 审计
• 表的同步复制
• 实施安全授权
• 防止无效的事务

创建触发器

要在表上创建触发器，用户必须具有 CREATE TRIGGER 权限。除此之外，用户必须具有 ALTER ANY TABLE 系统权限，或者必须是表的所有者，以便他可以更改该表。同样，如果用户希望更改任何触发器，那么他必须具有 ALTER ANY TRIGGER 系统权限，或者必须是触发器的所有者。

创建触发器的语法

此处，

• CREATE [OR REPLACE] TRIGGER trigger_name: 创建或替换具有 trigger_name 的现有触发器。
• {BEFORE | AFTER | INSTEAD OF}：指定触发器何时执行。INSTEAD OF 子句用于在视图上创建触发器。
• {INSERT [OR] | UPDATE [OR] | DELETE}: 指定 DML 操作。
• [OF col_name]: 指定要更新的列名。
• [ON table_name]: 指定与触发器关联的表的名称。
• [REFERENCING OLD AS o NEW AS n]: 允许您引用各种 DML 语句的新旧值，例如 INSERT、UPDATE 和 DELETE。
• [FOR EACH ROW]: 指定行级触发器，即，将为受影响的每一行执行触发器。否则，触发器将在 SQL 语句执行时只执行一次，这称为表级触发器。
• WHEN (condition)：为触发器将触发的行提供条件。此子句仅对行级触发器有效。

PL/SQL 触发器示例

让我们举一个简单的例子来演示触发器。在此示例中，我们使用以下 CUSTOMERS 表

创建表并拥有记录

创建触发器

让我们编写一个程序来为 CUSTOMERS 表创建一个行级触发器，该触发器将为在 CUSTOMERS 表上执行的 INSERT 或 UPDATE 或 DELETE 操作触发。此触发器将显示旧值和新值之间的薪资差异

在 SQL 提示符下执行上述代码后，会产生以下结果。

Trigger created.

通过过程检查薪资差异

使用以下代码在创建触发器后获取旧薪资、新薪资和薪资差异。

输出

Old salary: 20000
New salary: 25000
Salary difference: 5000
Old salary: 22000
New salary: 27000
Salary difference: 5000
Old salary: 24000
New salary: 29000
Salary difference: 5000
Old salary: 26000
New salary: 31000
Salary difference: 5000
Old salary: 28000
New salary: 33000
Salary difference: 5000
Old salary: 30000
New salary: 35000
Salary difference: 5000
6 customers updated 

注意：由于您多次执行此代码，旧的和新的薪资都会增加 5000，因此薪资差异始终为 5000。

再次执行上述代码后，您将获得以下结果。

Old salary: 25000
New salary: 30000
Salary difference: 5000
Old salary: 27000
New salary: 32000
Salary difference: 5000
Old salary: 29000
New salary: 34000
Salary difference: 5000
Old salary: 31000
New salary: 36000
Salary difference: 5000
Old salary: 33000
New salary: 38000
Salary difference: 5000
Old salary: 35000
New salary: 40000
Salary difference: 5000
6 customers updated

重要提示

以下是两个非常重要的点，应仔细注意。

• OLD 和 NEW 引用用于记录级别触发器，这些引用不适用于表级别触发器。
• 如果要在同一触发器中查询表，则应使用 AFTER 关键字，因为触发器只有在应用初始更改并且表恢复到一致状态后，才能查询表或再次更改它。

在触发器中使用条件谓词

有时您想知道哪个语句导致触发器触发。如果我们只在一种类型的语句上创建触发器，那将很容易。但如果它是触发器语句的组合，例如 INSERT OR UPDATE OR DELETE，那么很难找到哪种类型的语句导致触发器触发。因此，条件谓词用于确定哪个语句导致触发器触发。

这些是 INSERTING、UPDATING、DELETING。这些条件谓词中的每一个都返回 TRUE 或 FALSE，如下所述

• INSERTING：如果触发器是由 INSERT 插入到附加触发器的表而触发的，则返回 TRUE，否则返回 FALSE。
• UPDATING：如果触发器是由更新附加触发器的表而触发的，则返回 TRUE，否则返回 FALSE。
• DELETING：如果触发器是由从附加触发器的表中删除而触发的，则返回 TRUE，否则返回 FALSE。

示例 1：创建一个触发器，该触发器说明用户在 Emp_details 表上执行的操作。

输出

Following is the output of this example.
Trigger Created.
INSERT INTO Emp_Details (Id, Name, Job) VALUES (106, 'Binni', ‘Manager’);
1 row created.
DELETE FROM Emp_Details WHERE id = 101;
1 row deleted.

上面的触发器是一个语句触发器。因此，无论在执行语句时有多少行受到影响，触发器都只会触发一次，因为缺少 FOR EACH ROW 子句。

您还可以使用条件谓词来测试是否在 UPDATE 触发器中更新了某个特定列。

示例 2：创建一个触发器，将对 Emp 表所做的修改记录到 Emp_Log 表中。

输出

以下是这个例子的输出。

Trigger created
DELETE FROM Emp WHERE empno = 101;
1 row deleted.

如果用户在 emp 表上执行任何操作，如插入、更新或删除，则会将对 Emp 表所做的更改插入到 Emp_Log 表中。此示例展示了触发器如何帮助复制表。

启用和禁用触发器

PL/SQL 提供了许多 DDL 语句，可以帮助您管理触发器，您可以使用 ALTER TRIGGER 语句启用和禁用触发器。

当您需要进行一些特殊处理时，可以暂时禁用触发器。考虑一个示例，其中用户为每个行触发器编写了一个 BEFORE INSERT，该触发器执行数据格式化，例如将姓名和职务列的大小写更改为大写。如果我们打算加载大量数据，即数千行，并且我们确信数据已正确格式化。那么数据库没有理由触发一个实际上会花费时间但什么也做不了的触发器。因此，在这种情况下，我们可以使用以下语句禁用触发器。

语法

在上面的语法中，alter 是一个命令，disable 关键字用于禁用触发器。

因此，要禁用触发器 Emp_upper，请使用以下语句。

示例

数据加载完成后，您可以使用以下语句重新启用触发器。

语法

在上面的语法中，alter 是一个命令，enable 关键字用于启用触发器。

因此，用户可以启用 Emp_upper 触发器，使用以下语句。

示例

但是，如果用户希望禁用特定表上的所有触发器，而不是逐个禁用它们，则用户可以使用以下语句。

语法

删除触发器

我们可以使用 DROP 命令永久删除触发器。

语法

在上面的语法中，drop 是一个命令，trig_name 是您要删除的触发器的名称。

因此，如果用户希望删除触发器 Emp_Upper，则使用以下语句。

示例

触发器已删除。

从数据字典获取触发器信息

由于 PL/SQL 将其所有信息存储在其数据字典中。因此，要获取有关触发器的所有信息，我们使用数据字典 USER_TRIGGERS。USER_TRIGGERS 数据字典视图提供有关触发器名称、事件、其操作的表名、引用名称、触发器主体等的信息。

因此，要查看 Emp 表上的触发器名称和触发器事件及其文本，我们编写以下语句。

同样，如果用户希望检查触发器的有效性，则使用以下语句。

示例：创建一个触发器，该触发器在 Emp 表上执行插入、更新或删除操作时触发，并因此更新 Dept 表的“no_of_emp”列。

解决方案：由于 Dept 表不包含“no_of_emp”列，因此我们将使用以下语句将其添加到 dept 表。

现在我们将创建一个触发器。

输出

以下是这个例子的输出。

已创建触发器

创建触发器并在运行之前，您必须执行另一项工作。由于触发器维护员工计数，并且它们不会将其初始化为正确的值。因此，您必须使用 UPDATE 语句自己初始化计数。

Update Dept set no_emp = (select count (*) from emp where emp.deptno = dept.deptno);

已更新 1 行。

因此，员工的计数将初始化为 emp 表中已包含的员工数量。