PL/SQL触发器

触发器是由Oracle引擎在特定事件发生时自动调用的。当特定条件匹配时，触发器被存储在数据库中并重复调用。

触发器是存储程序，当发生某个事件时，它们将自动执行或触发。

触发器被编写为响应以下任何事件而执行。

• 数据库操作（DML）语句（DELETE，INSERT或UPDATE）。
• 数据库定义（DDL）语句（CREATE，ALTER或DROP）。
• 数据库操作（SERVERERROR，LOGON，LOGOFF，STARTUP或SHUTDOWN）。

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

触发器的优势

以下是触发器的优点：

• 触发器自动生成一些导出列的值
• 强制引用完整性
• 记录事件日志并存储表访问信息
• 审计
• 表的同步复制
• 实施安全授权
• 防止无效事务

创建触发器：

创建触发器的语法：

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

PL/SQL触发器示例

让我们以一个简单的示例来演示触发器。在此示例中，我们使用以下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表执行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:

由于这是一个新记录，旧的薪水不可用，所以上述结果为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