Plsql 简明教程

PL/SQL - Triggers

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

In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −

  1. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

  2. A database definition (DDL) statement (CREATE, ALTER, or DROP).

  3. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

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

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

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

Triggers can be written for the following purposes −

  1. Generating some derived column values automatically

  2. Enforcing referential integrity

  3. Event logging and storing information on table access

  4. Auditing

  5. Synchronous replication of tables

  6. Imposing security authorizations

  7. Preventing invalid transactions

Creating Triggers

  • 创建触发器的语法为：

The syntax for creating a trigger is −

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;

其中，

Where,

  1. CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.

  2. {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.

  3. {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

  4. [OF col_name] − This specifies the column name that will be updated.

  5. [ON table_name] − This specifies the name of the table associated with the trigger.

  6. [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.

  7. [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

  8. WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Example

首先，将使用在之前的章节中创建并使用的 CUSTOMERS 表：

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters −

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 操作触发。此触发器将显示新旧值之间的薪水差异：

The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values −

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Trigger created.

这里需要考虑以下几点：

The following points need to be considered here −

  1. OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.

  2. 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.

  3. 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

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

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

当在 CUSTOMERS 表中创建记录时，将触发上述创建触发器 display_salary_changes ，它将显示以下结果：

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:

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

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;

当在 CUSTOMERS 表中更新记录时，将触发上述创建触发器 display_salary_changes ，它将显示以下结果：

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