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表:
创建表并具有记录:
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
23 |
Allahabad |
20000 |
2 |
Suresh |
22 |
Kanpur |
22000 |
3 |
Mahesh |
24 |
Ghaziabad |
24000 |
4 |
Chandan |
25 |
Noida |
26000 |
5 |
Alex |
21 |
Paris |
28000 |
6 |
Sunita |
20 |
Delhi |
30000 |
创建触发器:
让我们为程序创建一个用于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 Prompt上执行上述代码后,将产生以下结果。
按过程检查工资差异:
使用以下代码获取触发器创建后的旧工资,新工资和工资差异。
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
if sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSif sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/
输出:
Old salary: 20000
New salary: 25000
Salary difference: 5000
Old salary: 22000
New salary: 27000
Salary difference: 5000
Old salary: 24000
New salary: 29000
Salary difference: 5000
Old salary: 26000
New salary: 31000
Salary difference: 5000
Old salary: 28000
New salary: 33000
Salary difference: 5000
Old salary: 30000
New salary: 35000
Salary difference: 5000
6 customers updated
注意: : 执行此代码的次数很多,新旧工资都增加了5000,因此工资差始终为5000。
执行后再次执行上述代码,您将获得以下结果。
Old salary: 25000
New salary: 30000
Salary difference: 5000
Old salary: 27000
New salary: 32000
Salary difference: 5000
Old salary: 29000
New salary: 34000
Salary difference: 5000
Old salary: 31000
New salary: 36000
Salary difference: 5000
Old salary: 33000
New salary: 38000
Salary difference: 5000
Old salary: 35000
New salary: 40000
Salary difference: 5000
6 customers updated
重要要点
以下是两个非常重要的要点,应仔细注意。
OLD和NEW引用用于记录级别的触发器,对于表级别的触发器则不可用。
如果要在同一触发器中查询表,则应使用AFTER关键字,因为触发器可以查询表或仅在应用初始更改并且表回到一致状态后再次更改表。