Plsql 简明教程

PL/SQL - Triggers

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

database manipulation (DML) 语句（DELETE、INSERT 或 UPDATE）
一个 database definition (DDL) 语句 (CREATE、ALTER 或 DROP)。
一个 database operation (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。

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

Benefits of Triggers

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

* 自动生成一些派生列的值
Enforcing referential integrity
* 事件日志和存储有关表访问的信息
Auditing
Synchronous replication of tables
Imposing security authorizations
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;

其中，

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) - 这为触发器将触发的行提供了一个条件。此子句仅对行级触发器有效。

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.

这里需要考虑以下几点：

* 旧值和新值引用不可用于表级触发器，但可以将其用于记录级触发器。
If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table −

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

When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −

Old salary:
New salary: 7500
Salary difference:

Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table −

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

When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −

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