上一节:
下一节:

  PL/SQL - 触发器

  • 简述

    在本章中，我们将讨论 PL/SQL 中的触发器。触发器是存储的程序，在某些事件发生时会自动执行或触发。事实上，触发器是为了响应以下任何事件而编写的 -
    • database manipulation (DML) 语句（删除、插入或更新）
    • database definition (DDL) 语句（CREATE、ALTER 或 DROP）。
    • database operation （服务器错误、登录、注销、启动或关闭）。
    可以在与事件关联的表、视图、模式或数据库上定义触发器。

    触发器的好处

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

  • 创建触发器

    创建触发器的语法是 - 
    
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 | 
+----+----------+-----+-----------+----------+
    下面的程序创建一个 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.
    这里需要考虑以下几点 -
    • 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:
    因为这是新记录，旧工资不可用，上述结果为空。现在让我们对 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
上一节:
下一节: