笙亿网络策划

PL/SQL Triggers 怎么创建和使用？

约 7 分钟读完 87 阅
文章导读
Previous Quiz Next 在本章中，我们将讨论 PL/SQL 中的 Triggers。Triggers 是存储的程序，当某些事件发生时，它们会自动执行或触发。Triggers 实际上是为响应以下任何事件而编写的 −
📋 目录
  1. A 创建触发器
  2. B 触发触发器
A A

PL/SQL - Triggers

Previous
Quiz
Next

在本章中，我们将讨论 PL/SQL 中的 Triggers。Triggers 是存储的程序，当某些事件发生时，它们会自动执行或触发。Triggers 实际上是为响应以下任何事件而编写的 −

  • 数据库操作 (DML) 语句 (DELETE、INSERT 或 UPDATE)

  • 数据库定义 (DDL) 语句 (CREATE、ALTER 或 DROP)。

  • 数据库操作 (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。

Triggers 可以定义在与事件关联的 table、view、schema 或 database 上。

Triggers 的优势

Triggers 可以为以下目的编写 −

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

创建触发器

创建触发器的语法如下 −

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

其中，

  • 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) − 为触发器提供触发条件。此子句仅适用于行级触发器。

示例

首先，我们将使用前几章中创建和使用的 CUSTOMERS 表 −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

以下程序为 customers 表创建一个 行级 触发器，该触发器会在对 CUSTOMERS 表执行 INSERT 或 UPDATE 或 DELETE 操作时触发。此触发器将显示旧值和新值之间的薪资差异 −

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('旧薪资: ' || :OLD.salary); 
   dbms_output.put_line('新薪资: ' || :NEW.salary); 
   dbms_output.put_line('薪资差异: ' || sal_diff); 
END; 
/

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

Trigger created.

此处需要注意以下几点 −

  • 表级触发器不可用 OLD 和 NEW 引用，而记录级触发器可以使用它们。

  • 如果要在同一触发器中查询表，则应使用 AFTER 关键字，因为只有在初始更改应用后表恢复到一致状态时，触发器才能查询表或再次更改它。

  • 上述触发器编写方式使其在表的任何 DELETE 或 INSERT 或 UPDATE 操作之前触发，但您可以为单个或多个操作编写触发器，例如 BEFORE DELETE，它会在使用表的 DELETE 操作删除记录时触发。

触发触发器

让我们对 CUSTOMERS 表执行一些 DML 操作。这里是一个 INSERT 语句，它将在表中创建一个新记录 −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

当在 CUSTOMERS 表中创建一条记录时，上述创建的触发器 display_salary_changes 将被触发，并显示以下结果 −

Old salary: 
New salary: 7500 
Salary difference:

因为这是一个新记录，旧工资不可用，因此上述结果显示为 null。现在让我们对 CUSTOMERS 表再执行一个 DML 操作。UPDATE 语句将更新表中的现有记录 −

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

当在 CUSTOMERS 表中更新一条记录时，上述创建的触发器 display_salary_changes 将被触发，并显示以下结果 −

Old salary: 1500 
New salary: 2000 
Salary difference: 500