Plsql 简明教程

PL/SQL - Triggers

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

  1. database manipulation (DML) 语句（DELETE、INSERT 或 UPDATE）

  2. 一个 database definition (DDL) 语句 (CREATE、ALTER 或 DROP)。

  3. 一个 database operation (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。

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

Benefits of Triggers

可以针对以下目的编写触发器：

  1. * 自动生成一些派生列的值

  2. Enforcing referential integrity

  3. * 事件日志和存储有关表访问的信息

  4. Auditing

  5. Synchronous replication of tables

  6. Imposing security authorizations

  7. Preventing invalid transactions

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

其中，

  1. CREATE [OR REPLACE] TRIGGER trigger_name - 创建或替换具有 trigger_name 的现有触发器。

  2. {BEFORE | AFTER | INSTEAD OF} - 这指定触发器将何时执行。INSTEAD OF 子句用于在视图上创建触发器。

  3. {INSERT [OR] | UPDATE [OR] | DELETE} - 这指定 DML 操作。

  4. [OF col_name] - 这指定将更新的列名。

  5. [ON table_name] - 这指定与触发器关联的表的名称。

  6. [REFERENCING OLD AS o NEW AS n] - 这允许你引用各种 DML 语句的新值和旧值，例如 INSERT、UPDATE 和 DELETE。

  7. [FOR EACH ROW] - 这指定一个行级触发器，即触发器将针对受影响的每一行执行。否则，触发器只会在执行 SQL 语句时执行一次，这称为表级触发器。

  8. WHEN (condition) - 这为触发器将触发的行提供了一个条件。此子句仅对行级触发器有效。

Example

首先，将使用在之前的章节中创建并使用的 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 表创建了一个 row-level 触发器，该触发器将在 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: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

Trigger created.

这里需要考虑以下几点：

  1. * 旧值和新值引用不可用于表级触发器，但可以将其用于记录级触发器。

  2. 如果您想在同一个触发器中查询表，那么您应该使用 AFTER 关键字，因为只有在应用了最初的更改且表已恢复到一致状态之后，触发器才可以查询表或再次对其进行更改。

  3. 上述触发器以这样一种方式进行编写：它将在表上的任何 DELETE 或 INSERT 或 UPDATE 操作之前触发，但你可以针对单个或多个操作编写触发器，例如“BEFORE DELETE”（在使用 DELETE 操作在表中删除记录时触发）。

Triggering a Trigger

让我们对 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