Oracle数据库数据编程SQL＜3.7 PL/SQL 触发器(Trigger)＞

触发器是Oracle数据库中的一种特殊存储过程，它会在特定数据库事件发生时主动执行。触发器通常用于实现复杂的业务规则、数据验证、审计跟踪等功能。
目录
一、触发器基本概念
1. 触发器特点
2. 触发器构成要素
二、触发器范例
1. DML触发器
2. DDL触发器
3. 体系/数据库事件触发器
4. INSTEAD OF触发器
三、创建DML触发器
1. 基本语法
2. 行级触发器示例
3. 语句级触发器示例
四、特殊触发器
1. 复合触发器(11g+)
2. INSTEAD OF触发器(用于视图)
3. 体系事件触发器
五、触发器中的特殊变量和函数
1. OLD 和 NEW 伪记录
2. 事件属性函数
3. 条件谓词
4. 简单示例
5. 简单练习
六、触发器管理
1. 检察触发器
2. 启用/禁用触发器
3. 重新编译触发器
4. 删除触发器
七、触发器最佳实践
八、常见问题办理方案
1. 制止触发器递归
2. 处置惩罚大批量操纵
3. 跨数据库同步
九、触发器使用注意事项

---

一、触发器基本概念

触发器在数据库里以独立的对象存储，他与存储过程不同的是，存储过程通过其他步伐来启动运行或者直接运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时主动地隐式运行，并且触发器不接收参数。 
1. 触发器特点

• 主动执行：满意条件时由数据库主动触发
  
• 事件驱动：响应特定DML或DDL操纵
  
• 无显式调用：不能像存储过程那样直接调用
  
• 事务感知：作为触发语句的一部门执行
  

2. 触发器构成要素

• 触发事件：INSERT、UPDATE、DELETE等DML操纵
  
• 触发机遇：BEFORE或AFTER
  
• 触发级别：行级(FOR EACH ROW)或语句级
  
• 触发条件：WHEN子句定义的条件
  
• 触发体：PL/SQL代码块
  

二、触发器范例

1. DML触发器

响应数据操纵语言(DML)事件：

• INSERT
  
• UPDATE
  
• DELETE
  
• MERGE (10g+)
  

2. DDL触发器

响应数据定义语言(DDL)事件：

• CREATE
  
• ALTER
  
• DROP
  
• TRUNCATE等
  

3. 体系/数据库事件触发器

响应数据库体系事件：

• 登录/注销(LOGON/LOGOFF)
  
• 服务器错误(SERVERERROR)
  
• 启动/关闭(STARTUP/SHUTDOWN)
  

4. INSTEAD OF触发器

用于视图上的DML操纵
三、创建DML触发器

语句级触发器(statement)：当某触发事件发生时，该触发器执行一次
行级触发器(row)：当某触发事件发生时，受到影响的每一行数据，触发器都单独执行一次
1. 基本语法

1. CREATE [OR REPLACE] TRIGGER trigger_name
2. {BEFORE | AFTER | INSTEAD OF}
3. {INSERT | UPDATE | DELETE [OR INSERT | UPDATE | DELETE...]}
4. ON {table_name | view_name}
5. [REFERENCING [OLD AS old] [NEW AS new]]
6. [FOR EACH ROW]
7. [WHEN (condition)]
8. [DECLARE
9.     declaration_statements]
10. BEGIN
11.     executable_statements
12. [EXCEPTION
13.     exception_handling_statements]
14. END [trigger_name];
16. --化说明
17. create or replace trigger tri_name--创建触发器
18. {before/after}--触发的时间点{DML操作} on 表名
19. {for each row}--加上是行级触发，不加是语句级
20. {when 条件}
21. begin
22. {referencing {OLD {as} old|new {as} NEW}new PARENT as parent}---触发器的执行部分
23. end;
24. /
26. -- 简单示例1
27. create or replace trigger tr_1
28.   before delete on emp11
29. begin
30.   insert into emp12
31.     (ename)
32.     select ename from emp where empno = 7369;
33. end;
34. /
35. -- 简单示例2
36. --创建一个触发器 当删除emp1表中的数据时，向emp2插入7788的员工信息
37. --并且删除emp3中7788的信息。
38. -- 准备
39. create table emp1 as select * from emp;
40. create table emp2 as select * from emp;
41. create table emp3 as select * from emp;
42. -- 创建触发器
43. create or replace trigger tr_1
44.   before delete on emp1
45. begin
46.   insert into emp2
47.     (ename, sal)
48.     select ename, sal from emp where empno = 7788;
49.   delete from emp3 where empno = 7788;
50. end;
51. /
52. -- 触发
53. delete from emp1 where empno=7788;

复制代码

2. 行级触发器示例

1. -- 薪资变更审计触发器
2. CREATE OR REPLACE TRIGGER audit_salary_change
3. BEFORE UPDATE OF salary ON employees
4. FOR EACH ROW
5. WHEN (NEW.salary <> OLD.salary)
6. DECLARE
7.     v_change_type VARCHAR2(10);
8. BEGIN
9.     -- 确定变更类型
10.     IF :NEW.salary > :OLD.salary THEN
11.         v_change_type := 'RAISE';
12.     ELSE
13.         v_change_type := 'CUT';
14.     END IF;
15.    
16.     -- 记录审计信息
17.     INSERT INTO salary_audit (
18.         audit_id, employee_id,
19.         old_salary, new_salary,
20.         change_type, change_date, changed_by
21.     ) VALUES (
22.         audit_seq.NEXTVAL, :NEW.employee_id,
23.         :OLD.salary, :NEW.salary,
24.         v_change_type, SYSDATE, USER
25.     );
26. END audit_salary_change;
27. /

复制代码

3. 语句级触发器示例

1. -- 限制非工作时间操作
2. CREATE OR REPLACE TRIGGER restrict_after_hours_dml
3. BEFORE INSERT OR UPDATE OR DELETE ON employees
4. DECLARE
5.     v_current_time VARCHAR2(8) := TO_CHAR(SYSDATE, 'HH24:MI:SS');
6.     v_day_of_week VARCHAR2(3) := TO_CHAR(SYSDATE, 'DY');
7. BEGIN
8.     -- 工作日晚上8点到早上6点禁止操作
9.     IF v_day_of_week NOT IN ('SAT', 'SUN')
10.        AND (v_current_time > '20:00:00' OR v_current_time < '06:00:00') THEN
11.         RAISE_APPLICATION_ERROR(-20001,
12.             '非工作时间(工作日6:00-20:00)不允许修改员工数据');
13.     END IF;
14. END restrict_after_hours_dml;
15. /

复制代码

四、特殊触发器

1. 复合触发器(11g+)

1. CREATE OR REPLACE TRIGGER compound_emp_trigger
2. FOR INSERT OR UPDATE OR DELETE ON employees
3. COMPOUND TRIGGER
4.     -- 在触发语句开始前执行
5.     BEFORE STATEMENT IS
6.     BEGIN
7.         DBMS_OUTPUT.PUT_LINE('开始员工数据变更操作');
8.     END BEFORE STATEMENT;
9.    
10.     -- 每行变更前执行
11.     BEFORE EACH ROW IS
12.     BEGIN
13.         DBMS_OUTPUT.PUT_LINE('准备变更员工ID: ' ||
14.                             NVL(:NEW.employee_id, :OLD.employee_id));
15.     END BEFORE EACH ROW;
16.    
17.     -- 每行变更后执行
18.     AFTER EACH ROW IS
19.     BEGIN
20.         DBMS_OUTPUT.PUT_LINE('已完成变更员工ID: ' ||
21.                             NVL(:NEW.employee_id, :OLD.employee_id));
22.     END AFTER EACH ROW;
23.    
24.     -- 在触发语句结束后执行
25.     AFTER STATEMENT IS
26.     BEGIN
27.         DBMS_OUTPUT.PUT_LINE('员工数据变更操作完成');
28.     END AFTER STATEMENT;
29. END compound_emp_trigger;
30. /

复制代码

2. INSTEAD OF触发器(用于视图)

1. -- 创建复杂视图
2. CREATE OR REPLACE VIEW emp_dept_view AS
3. SELECT e.employee_id, e.last_name, e.salary,
4.        d.department_id, d.department_name
5. FROM employees e JOIN departments d
6. ON e.department_id = d.department_id;
8. -- 创建INSTEAD OF触发器支持插入
9. CREATE OR REPLACE TRIGGER io_emp_dept_insert
10. INSTEAD OF INSERT ON emp_dept_view
11. FOR EACH ROW
12. DECLARE
13.     v_dept_id NUMBER;
14. BEGIN
15.     -- 检查部门是否存在
16.     BEGIN
17.         SELECT department_id INTO v_dept_id
18.         FROM departments
19.         WHERE department_name = :NEW.department_name;
20.     EXCEPTION
21.         WHEN NO_DATA_FOUND THEN
22.             RAISE_APPLICATION_ERROR(-20001,
23.                 '部门 ' || :NEW.department_name || ' 不存在');
24.     END;
25.    
26.     -- 插入员工记录
27.     INSERT INTO employees (
28.         employee_id, last_name, salary, department_id
29.     ) VALUES (
30.         employees_seq.NEXTVAL, :NEW.last_name,
31.         :NEW.salary, v_dept_id
32.     );
33. END io_emp_dept_insert;
34. /

复制代码

3. 体系事件触发器

1. -- 登录审计触发器
2. CREATE OR REPLACE TRIGGER logon_audit_trigger
3. AFTER LOGON ON DATABASE
4. BEGIN
5.     INSERT INTO logon_audit (
6.         audit_id, username,
7.         logon_time, host, ip_address
8.     ) VALUES (
9.         logon_seq.NEXTVAL, USER,
10.         SYSDATE, SYS_CONTEXT('USERENV', 'HOST'),
11.         SYS_CONTEXT('USERENV', 'IP_ADDRESS')
12.     );
13. END logon_audit_trigger;
14. /
16. -- DDL变更审计触发器
17. CREATE OR REPLACE TRIGGER audit_ddl_changes
18. AFTER DDL ON SCHEMA
19. BEGIN
20.     INSERT INTO ddl_audit (
21.         audit_id, username,
22.         event_type, object_type,
23.         object_name, change_date
24.     ) VALUES (
25.         ddl_audit_seq.NEXTVAL, USER,
26.         ORA_SYSEVENT, ORA_DICT_OBJ_TYPE,
27.         ORA_DICT_OBJ_NAME, SYSDATE
28.     );
29. END audit_ddl_changes;
30. /

复制代码

五、触发器中的特殊变量和函数

1. OLD 和 NEW 伪记录

• :OLD：引用操纵前的行值(UPDATE/DELETE)---old.列名：此列中的旧值
  
• :NEW：引用操纵后的行值(INSERT/UPDATE)---new.列名：此列中的新值
  

2. 事件属性函数

• ORA_SYSEVENT：触发事件的名称
  
• ORA_DICT_OBJ_TYPE：DDL操纵的对象范例
  
• ORA_DICT_OBJ_NAME：DDL操纵的对象名称
  
• ORA_IS_ALTER_COLUMN：检查是否修改了特定列
  

3. 条件谓词

• INSERTING：触发器由INSERT触发时为TRUE
  
• UPDATING：触发器由UPDATE触发时为TRUE
  
• DELETING：触发器由DELETE触发时为TRUE
  

条件谓语	old	new
update	有	有
delete	有	null
insert	null	有

4. 简单示例

1. --创建一个触发器
2. --当emp1被更新是,将更新前和更新后的ename和empno插入到emp2
3. --的对应列，在job列显示更新前还是更新后,并且hiredate列插入更新时间
4. CREATE OR REPLACE TRIGGER TRE_1
5.   BEFORE UPDATE ON EMP1
6.   FOR EACH ROW
7. BEGIN
8.   INSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:old.ename,:old.empno,'更新前',sysdate);
9.   INSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:new.ename,:new.empno,'更新后',SYSDATE);
10. END;
12. TRUNCATE TABLE emp2
13. UPDATE emp1 SET sal=sal+10000;
14. SELECT * FROM emp2

复制代码

特性	INSERT	UPDATE	DELETE
OLD	NULL	现实值	现实值
NEW	现实值	现实值	NULL

5. 简单练习

1. --创建一张emp1和emp内容一样。创建emp2只保留emp的格式。
2. --创建一个触发器
3. --当emp1表被更新时触发，将更新前和更新后的ename,empno插入到emp2中
4. -- 创建触发器
5. create or replace trigger tr_3
6. before update on emp1
7. for each row
8.   begin
9.     insert into emp2 (ename,empno,job) values(:old.ename,:old.empno,'old');
10.     insert into emp2 (ename,empno,job) values(:new.ename,:new.empno,'new');
11. end;
12. /
13. -- 触发
14. update emp1 set empno=empno-7000,ename=lower(ename);
15. -- 查询验证变化
16. select * from emp1;
17. select * from emp2;

复制代码

六、触发器管理

1. 检察触发器

1. -- 查看触发器定义
2. SELECT trigger_name, trigger_type, triggering_event, table_name, status
3. FROM user_triggers;
5. -- 查看触发器源代码
6. SELECT text FROM user_source
7. WHERE name = 'AUDIT_SALARY_CHANGE' AND type = 'TRIGGER'
8. ORDER BY line;

复制代码

2. 启用/禁用触发器

1. -- 禁用单个触发器
2. ALTER TRIGGER audit_salary_change DISABLE;
4. -- 启用单个触发器
5. ALTER TRIGGER audit_salary_change ENABLE;
7. -- 禁用表上的所有触发器
8. ALTER TABLE employees DISABLE ALL TRIGGERS;
10. -- 启用表上的所有触发器
11. ALTER TABLE employees ENABLE ALL TRIGGERS;

复制代码

3. 重新编译触发器

1. ALTER TRIGGER trigger_name COMPILE;

复制代码

4. 删除触发器

1. DROP TRIGGER trigger_name;

复制代码

七、触发器最佳实践

• 保持简便：触发器应简短高效，制止复杂业务逻辑
  
• 制止递归：注意触发器大概导致的级联触发
  
• 思量性能：行级触发器对大批量操纵影响较大
  
• 明确文档：记录触发器目的和业务规则
  
• 非常处置惩罚：妥善处置惩罚大概出现的错误
  
• 制止事务控制：通常不应在触发器中提交或回滚
  
• 测试充分：验证触发器在各种场景下的举动
  

八、常见问题办理方案

1. 制止触发器递归

1. CREATE OR REPLACE TRIGGER prevent_recursion
2. BEFORE UPDATE ON employees
3. FOR EACH ROW
4. DECLARE
5.     v_recursion_flag BOOLEAN := FALSE;
6. BEGIN
7.     -- 检查是否由触发器调用
8.     IF UPDATING AND DBMS_UTILITY.FORMAT_CALL_STACK LIKE '%PREVENT_RECURSION%' THEN
9.         v_recursion_flag := TRUE;
10.     END IF;
11.    
12.     -- 如果不是递归调用，则执行业务逻辑
13.     IF NOT v_recursion_flag THEN
14.         -- 业务逻辑代码
15.     END IF;
16. END;
17. /

复制代码

2. 处置惩罚大批量操纵

1. -- 使用BULK COLLECT和FORALL优化
2. CREATE OR REPLACE TRIGGER optimize_bulk_operation
3. AFTER INSERT ON large_table
4. DECLARE
5.     TYPE id_array IS TABLE OF large_table.id%TYPE;
6.     v_ids id_array;
7. BEGIN
8.     -- 批量收集新插入的ID
9.     SELECT id BULK COLLECT INTO v_ids
10.     FROM large_table
11.     WHERE status = 'NEW';
12.    
13.     -- 批量处理
14.     FORALL i IN 1..v_ids.COUNT
15.         UPDATE related_table
16.         SET last_updated = SYSDATE
17.         WHERE large_table_id = v_ids(i);
18. END;
19. /

复制代码

3. 跨数据库同步

1. CREATE OR REPLACE TRIGGER sync_cross_database
2. AFTER INSERT OR UPDATE OR DELETE ON local_table
3. FOR EACH ROW
4. DECLARE
5.     PRAGMA AUTONOMOUS_TRANSACTION;
6. BEGIN
7.     IF INSERTING THEN
8.         INSERT INTO remote_table@remote_db VALUES (:NEW.id, :NEW.name);
9.     ELSIF UPDATING THEN
10.         UPDATE remote_table@remote_db
11.         SET name = :NEW.name
12.         WHERE id = :OLD.id;
13.     ELSIF DELETING THEN
14.         DELETE FROM remote_table@remote_db WHERE id = :OLD.id;
15.     END IF;
16.     COMMIT;
17. EXCEPTION
18.     WHEN OTHERS THEN
19.         ROLLBACK;
20.         -- 记录错误但不中断主事务
21.         INSERT INTO error_log VALUES (SYSDATE, 'sync_cross_database', SQLERRM);
22.         COMMIT;
23. END;
24. /

复制代码

九、触发器使用注意事项

编写触发器时，必要注意一下几点：
（1）触发器不担当参数
（2）一个表上最多可有12个触发器，但同一时间、同一事件、同一范例的触发器只能有一个。并且各触发器之间不能有矛盾。
（3）在一个表上的触发器越多，对在该表上的DML操纵的性能影响就越大
（4）触发器最大为32KB。若确实必要，可以先建立过程，然后在触发器中用CALL语句进行调用
（5）在触发器的执行部门只能用DML语句（SELECT、INSERT、UDATE、DELETE）,不能使用DDL语句（CREATE、ALTER、DROP）
（6）触发器中不能包罗事物控制语句（COMMIT、ROLLBACK、SAVEPOINT）。因为触发器是触发语句的一部门，触发语句被提交、回退时，触发器也被提交、回退了。
（7）在触发器主体中调用的任何过程、函数，都不能使用事务控制语句
（8）在触发器主体中不能声明任何Long和Blob变量。新值new和旧值old也不能向表中的任何Long和Blob列。
（9）不同范例的触发器（如DML触发器、INSTEAD OF触发器、体系触发器）的语法格式和作用有比力大区别
触发器是Oracle数据库强大的功能，公道使用可以实现复杂的业务规则、数据完备性和审计需求。但也需谨慎使用，制止过度依赖触发器导致体系难以维护。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告