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. 基本语法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE [OR INSERT | UPDATE | DELETE...]}
ON {table_name | view_name}
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW]
[WHEN (condition)]
[DECLAREdeclaration_statements]
BEGINexecutable_statements
[EXCEPTIONexception_handling_statements]
END [trigger_name];--化说明
create or replace trigger tri_name--创建触发器
{before/after}--触发的时间点{DML操作} on 表名
{for each row}--加上是行级触发，不加是语句级
{when 条件}
begin
{referencing {OLD {as} old|new {as} NEW}new PARENT as parent}---触发器的执行部分
end;
/-- 简单示例1
create or replace trigger tr_1before delete on emp11
begininsert into emp12(ename)select ename from emp where empno = 7369;
end;
/
-- 简单示例2
--创建一个触发器 当删除emp1表中的数据时，向emp2插入7788的员工信息
--并且删除emp3中7788的信息。
-- 准备
create table emp1 as select * from emp;
create table emp2 as select * from emp;
create table emp3 as select * from emp;
-- 创建触发器
create or replace trigger tr_1before delete on emp1
begininsert into emp2(ename, sal)select ename, sal from emp where empno = 7788;delete from emp3 where empno = 7788;
end;
/
-- 触发
delete from emp1 where empno=7788;

2. 行级触发器示例

-- 薪资变更审计触发器
CREATE OR REPLACE TRIGGER audit_salary_change
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
DECLAREv_change_type VARCHAR2(10);
BEGIN-- 确定变更类型IF :NEW.salary > :OLD.salary THENv_change_type := 'RAISE';ELSEv_change_type := 'CUT';END IF;-- 记录审计信息INSERT INTO salary_audit (audit_id, employee_id, old_salary, new_salary,change_type, change_date, changed_by) VALUES (audit_seq.NEXTVAL, :NEW.employee_id,:OLD.salary, :NEW.salary,v_change_type, SYSDATE, USER);
END audit_salary_change;
/

3. 语句级触发器示例

-- 限制非工作时间操作
CREATE OR REPLACE TRIGGER restrict_after_hours_dml
BEFORE INSERT OR UPDATE OR DELETE ON employees
DECLAREv_current_time VARCHAR2(8) := TO_CHAR(SYSDATE, 'HH24:MI:SS');v_day_of_week VARCHAR2(3) := TO_CHAR(SYSDATE, 'DY');
BEGIN-- 工作日晚上8点到早上6点禁止操作IF v_day_of_week NOT IN ('SAT', 'SUN') AND (v_current_time > '20:00:00' OR v_current_time < '06:00:00') THENRAISE_APPLICATION_ERROR(-20001, '非工作时间(工作日6:00-20:00)不允许修改员工数据');END IF;
END restrict_after_hours_dml;
/

四、特殊触发器

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

CREATE OR REPLACE TRIGGER compound_emp_trigger
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER-- 在触发语句开始前执行BEFORE STATEMENT ISBEGINDBMS_OUTPUT.PUT_LINE('开始员工数据变更操作');END BEFORE STATEMENT;-- 每行变更前执行BEFORE EACH ROW ISBEGINDBMS_OUTPUT.PUT_LINE('准备变更员工ID: ' || NVL(:NEW.employee_id, :OLD.employee_id));END BEFORE EACH ROW;-- 每行变更后执行AFTER EACH ROW ISBEGINDBMS_OUTPUT.PUT_LINE('已完成变更员工ID: ' || NVL(:NEW.employee_id, :OLD.employee_id));END AFTER EACH ROW;-- 在触发语句结束后执行AFTER STATEMENT ISBEGINDBMS_OUTPUT.PUT_LINE('员工数据变更操作完成');END AFTER STATEMENT;
END compound_emp_trigger;
/

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

-- 创建复杂视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
FROM employees e JOIN departments d 
ON e.department_id = d.department_id;-- 创建INSTEAD OF触发器支持插入
CREATE OR REPLACE TRIGGER io_emp_dept_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
DECLAREv_dept_id NUMBER;
BEGIN-- 检查部门是否存在BEGINSELECT department_id INTO v_dept_idFROM departmentsWHERE department_name = :NEW.department_name;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR(-20001, '部门 ' || :NEW.department_name || ' 不存在');END;-- 插入员工记录INSERT INTO employees (employee_id, last_name, salary, department_id) VALUES (employees_seq.NEXTVAL, :NEW.last_name, :NEW.salary, v_dept_id);
END io_emp_dept_insert;
/

3. 系统事件触发器

-- 登录审计触发器
CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON ON DATABASE
BEGININSERT INTO logon_audit (audit_id, username, logon_time, host, ip_address) VALUES (logon_seq.NEXTVAL, USER,SYSDATE, SYS_CONTEXT('USERENV', 'HOST'),SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END logon_audit_trigger;
/-- DDL变更审计触发器
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER DDL ON SCHEMA
BEGININSERT INTO ddl_audit (audit_id, username, event_type, object_type,object_name, change_date) VALUES (ddl_audit_seq.NEXTVAL, USER,ORA_SYSEVENT, ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_NAME, SYSDATE);
END audit_ddl_changes;
/

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

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. 简单示例

--创建一个触发器
--当emp1被更新是,将更新前和更新后的ename和empno插入到emp2
--的对应列，在job列显示更新前还是更新后,并且hiredate列插入更新时间
CREATE OR REPLACE TRIGGER TRE_1BEFORE UPDATE ON EMP1FOR EACH ROW
BEGININSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:old.ename,:old.empno,'更新前',sysdate);INSERT INTO EMP2 (ENAME, EMPNO,job,hiredate) VALUES (:new.ename,:new.empno,'更新后',SYSDATE);
END;TRUNCATE TABLE emp2
UPDATE emp1 SET sal=sal+10000;
SELECT * FROM emp2

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

5. 简单练习

--创建一张emp1和emp内容一样。创建emp2只保留emp的格式。
--创建一个触发器
--当emp1表被更新时触发，将更新前和更新后的ename,empno插入到emp2中
-- 创建触发器
create or replace trigger tr_3
before update on emp1
for each rowbegininsert into emp2 (ename,empno,job) values(:old.ename,:old.empno,'old');insert into emp2 (ename,empno,job) values(:new.ename,:new.empno,'new');
end;
/
-- 触发
update emp1 set empno=empno-7000,ename=lower(ename);
-- 查询验证变化
select * from emp1;
select * from emp2;

六、触发器管理

1. 查看触发器

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

2. 启用/禁用触发器

-- 禁用单个触发器
ALTER TRIGGER audit_salary_change DISABLE;-- 启用单个触发器
ALTER TRIGGER audit_salary_change ENABLE;-- 禁用表上的所有触发器
ALTER TABLE employees DISABLE ALL TRIGGERS;-- 启用表上的所有触发器
ALTER TABLE employees ENABLE ALL TRIGGERS;

3. 重新编译触发器

ALTER TRIGGER trigger_name COMPILE;

4. 删除触发器

DROP TRIGGER trigger_name;

七、触发器最佳实践

1. 保持简洁：触发器应简短高效，避免复杂业务逻辑

2. 避免递归：注意触发器可能导致的级联触发

3. 考虑性能：行级触发器对大批量操作影响较大

4. 明确文档：记录触发器目的和业务规则

5. 异常处理：妥善处理可能出现的错误

6. 避免事务控制：通常不应在触发器中提交或回滚

7. 测试充分：验证触发器在各种场景下的行为

八、常见问题解决方案

1. 避免触发器递归

CREATE OR REPLACE TRIGGER prevent_recursion
BEFORE UPDATE ON employees
FOR EACH ROW
DECLAREv_recursion_flag BOOLEAN := FALSE;
BEGIN-- 检查是否由触发器调用IF UPDATING AND DBMS_UTILITY.FORMAT_CALL_STACK LIKE '%PREVENT_RECURSION%' THENv_recursion_flag := TRUE;END IF;-- 如果不是递归调用，则执行业务逻辑IF NOT v_recursion_flag THEN-- 业务逻辑代码END IF;
END;
/

2. 处理大批量操作

-- 使用BULK COLLECT和FORALL优化
CREATE OR REPLACE TRIGGER optimize_bulk_operation
AFTER INSERT ON large_table
DECLARETYPE id_array IS TABLE OF large_table.id%TYPE;v_ids id_array;
BEGIN-- 批量收集新插入的IDSELECT id BULK COLLECT INTO v_idsFROM large_tableWHERE status = 'NEW';-- 批量处理FORALL i IN 1..v_ids.COUNTUPDATE related_tableSET last_updated = SYSDATEWHERE large_table_id = v_ids(i);
END;
/

3. 跨数据库同步

CREATE OR REPLACE TRIGGER sync_cross_database
AFTER INSERT OR UPDATE OR DELETE ON local_table
FOR EACH ROW
DECLAREPRAGMA AUTONOMOUS_TRANSACTION;
BEGINIF INSERTING THENINSERT INTO remote_table@remote_db VALUES (:NEW.id, :NEW.name);ELSIF UPDATING THENUPDATE remote_table@remote_dbSET name = :NEW.nameWHERE id = :OLD.id;ELSIF DELETING THENDELETE FROM remote_table@remote_db WHERE id = :OLD.id;END IF;COMMIT;
EXCEPTIONWHEN OTHERS THENROLLBACK;-- 记录错误但不中断主事务INSERT INTO error_log VALUES (SYSDATE, 'sync_cross_database', SQLERRM);COMMIT;
END;
/

九、触发器使用注意事项

编写触发器时，需要注意一下几点：

（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数据库强大的功能，合理使用可以实现复杂的业务规则、数据完整性和审计需求。但也需谨慎使用，避免过度依赖触发器导致系统难以维护。