时间：2021-07-01 10:21:17 帮助过：16人阅读
触发器是一种特殊的过程，它的执行是由一系列事件触发的。这些事件有用户登录注销事件，DML语句执行事件等等。我们常用的就是DML触发器，事件的精确粒度可以是表级或行级。
trigger的实际应用非常广泛，例如A表有数据DML操作，就可以在A表上做个触发器，将数据协同更新到B表。我们也经常用触发器来做审计。
触发器可分为：
创建触发器语法
CREATE OR REPLACE TRIGGER trigger_name {BEFORE|AFTER|INSTEAD OF} EVENTs ON TABLE_NAME [for each row] declare ... begin ... end;
检测的谓词可以直接用于 if 条件
查看触发器的数据字典
演示1：禁止DML操作T1表 触发器直接抛出错误中断DML操作
SQL> create table t1 as select * from emp; Table created. SQL> create or replace trigger tr_no_dml
before update or delete or insert
on t1 begin if updating then raise_application_error(-20001,‘不允许做UPDATE操作!‘); elsif inserting then raise_application_error(-20002,‘不允许做insert操作!‘); else raise_application_error(-20003,‘不允许做delete操作!‘); end if; end; Trigger created. SQL> update t1 set sal=sal+1 where empno=7788; update t1 set sal=sal+1 where empno=7788 * ERROR at line 1: ORA-20001: 不允许做UPDATE操作! ORA-06512: at "SCOTT.TR_NO_DML", line 4 ORA-04088: error during execution of trigger ‘SCOTT.TR_NO_DML‘ SQL> delete t1 where empno=7788; delete t1 where empno=7788 * ERROR at line 1: ORA-20003: 不允许做delete操作! ORA-06512: at "SCOTT.TR_NO_DML", line 9 ORA-04088: error during execution of trigger ‘SCOTT.TR_NO_DML‘ SQL> insert into t1 select * from emp where empno=7788; insert into t1 select * from emp where empno=7788 * ERROR at line 1: ORA-20002: 不允许做insert操作! ORA-06512: at "SCOTT.TR_NO_DML", line 7 ORA-04088: error during execution of trigger ‘SCOTT.TR_NO_DML‘
演示2：更新t2表，将更新记录在aud_tmp表
SQL> create table t2 as select * from emp; Table created. SQL> SQL> create table aud_tmp(exec_time date,exec_type varchar2(10),exec_user varchar2(20)); Table created. ----获取用户和时间的方法: SQL> select sys_context(‘USERENV‘,‘CURRENT_USER‘),sysdate from dual; SYS_CONTEXT(‘USERENV‘,‘CURRENT_USER‘) SYSDATE ------------------------------------------- ------------------- SCOTT 2011-03-11 05:37:06 SQL> SQL> create or replace trigger tri_t2_after_row after update or insert or delete on t2 for each row begin if updating then insert into aud_tmp values(sysdate,‘update‘,SYS_context(‘USERENV‘,‘CURRENT_USER‘)); elsif inserting then insert into aud_tmp values(sysdate,‘insert‘,SYS_context(‘USERENV‘,‘CURRENT_USER‘)); else insert into aud_tmp values(sysdate,‘delete‘,SYS_context(‘USERENV‘,‘CURRENT_USER‘)); end if; end; / Trigger created. SQL> select * from aud_tmp; no rows selected SQL> update t2 set sal=sal+1 where empno=7788; 1 row updated. SQL> select * from aud_tmp; EXEC_TIME EXEC_TYPE EXEC_USER ------------------- ---------- -------------------- 2011-03-11 05:39:32 SCOTT update SQL>
演示如下：
SQL> create table t3 as select * from emp; Table created. SQL>create or replace trigger tr_tab_t3 before update on t3 begin dbms_output.put_line(‘run tab tr‘); end; SQL>create or replace trigger tr_row_t3 before update on t3 for each row begin dbms_output.put_line(‘run row tr‘); end; / SQL> update t3 set sal=sal+1 where empno=7788; run tab tr run row tr /*单行操作 表级触发一次 行级触发一次*/ 1 row updated. SQL> SQL> update t3 set sal=sal+1 where deptno=10; run tab tr run row tr run row tr run row tr /*多行操作 表级触发一次 行级触发次数由操作行数来决定*/ 3 rows updated.
结论：
before statement trigger(on table)
->before row trigger (on table for each row)
->after row trigger (on table for each row)
->after statement trigger (on table)
表前==>行前==>行后==>表后
演示：
create or replace trigger e_update1 before update on e begin if updating then dbms_output.put_line(‘table before‘); end if; end; / create or replace trigger e_update2 before update on e for each row begin if updating then dbms_output.put_line(‘row before‘); end if; end; / create or replace trigger e_update3 after update on e for each row begin if updating then dbms_output.put_line(‘row after‘); end if; end; / create or replace trigger e_update4 after update on e begin if updating then dbms_output.put_line(‘table after‘); end if; end; / update e set sal=sal+1 where empno=7369;
--验证触发器的状态 select trigger_name,status from user_triggers; --禁用某个触发器 ALTER TRIGGER e_update3 disable; --禁用某个表上的所有触发器 alter table e disable all triggers; --删除触发器 DROP TRIGGER e_update3;
行级触发器增加了更新动作的旧值和新值的存储，表级不可以，行级触发器才有 :old.COL 和 :new.COL 的定义。
演示：
SQL> create or replace trigger tri_t4 after update or delete or insert on t4 for each row begin if updating then dbms_output.put_line(‘old_value: ‘||:old.sal||‘ ‘||‘new_value: ‘||:new.sal); elsif inserting then dbms_output.put_line(‘old_value: ‘||:old.sal||‘ ‘||‘new_value: ‘||:new.sal); else dbms_output.put_line(‘old_value: ‘||:old.sal||‘ ‘||‘new_value: ‘||:new.sal); end if; end; SQL> / Trigger created. SQL> update t4 set sal=sal+1 where ename=‘SCOTT‘; old_value: 3000 new_value: 3001 1 row updated. SQL> delete t4 where ename=‘SCOTT‘; old_value: 3001 new_value: 1 row deleted. SQL> insert into t4 select * from emp where ename=‘SCOTT‘; old_value: new_value: 3000 1 row created. SQL>
DML（delete/update/insert）触发器中不能使用DDL（CREATE，DROP，ALTER）语句，也不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有事物控制语句，否则会报错误:ORA-04092: cannot COMMIT in a trigger
trigger不能含有事物处理的语句，需要使用自制事务。自制事务可以将触发器内的DML独立成事务。
自制事务在declare中声明PRAGMA autonomous_transaction; pragma为编译指示。
切记:如果触发器中引用的过程也带有DDL DCL也要定义成自制事务。
不使用自制事务，拿上面做过的T2表触发器做演示
create or replace trigger tr_aud_t2 after update on t2 begin insert into aud_tmp values(sysdate,sys_context(‘USERENV‘,‘CURRENT_USER‘),‘update‘); commit; end; SQL> / Trigger created. SQL> select ename,sal from t2 where empno=7788; ENAME SAL ---------- ---------- SCOTT 102 SQL> update t2 set sal=sal+1 where empno=7788; update t2 set sal=sal+1 where empno=7788 * ERROR at line 1: ORA-04092: cannot COMMIT in a trigger ORA-06512: at "SCOTT.TR_AUD_T2", line 3 ORA-04088: error during execution of trigger ‘SCOTT.TR_AUD_T2‘
对T2执行更新时，会报错触发器不能使用commit语句。
改成自制事务
create or replace trigger tr_aud_t2 after update on t2 declare PRAGMA autonomous_transaction; begin insert into aud_tmp values(sysdate,sys_context(‘USERENV‘,‘CURRENT_USER‘),‘update‘); commit; end; SQL> / SQL> select ename,sal from t2 where empno=7788; ENAME SAL ---------- ---------- SCOTT 102 SQL> select * from aud_tmp; EXEC_TIME EXEC_TYPE EXEC_USER ------------------- ---------- -------------------- 2011-03-11 05:39:32 SCOTT update SQL> update t2 set sal=sal+1 where empno=7788; 1 row updated. SQL> select ename,sal from t2 where empno=7788; ENAME SAL ---------- ---------- SCOTT 103 SQL> select * from aud_tmp; EXEC_TIME EXEC_TYPE EXEC_USER ------------------- ---------- -------------------- 2011-03-11 05:39:32 SCOTT update 2011-03-11 06:02:04 SCOTT update SQL> roll Rollback complete. SQL> select ename,sal from t2 where empno=7788; ENAME SAL ---------- ---------- SCOTT 102 SQL> select * from aud_tmp; EXEC_TIME EXEC_TYPE EXEC_USER ------------------- ---------- -------------------- 2011-03-11 05:39:32 SCOTT update 2011-03-11 06:02:04 SCOTT update SQL>
这样，触发器提交的只有触发器内的事务(AUD_tmp表的插入)，而T2表的更新被回滚也可以记录下来。
替代触发器作用在视图上，把单条的DML分解成多条DML到多个表，就可以实现插入了，但带有聚集函数的还是不能操作。
局限性：只能更新两张表的简单视图。
演示如下：
----关闭d、e表之前我们做的所有触发器 以免影响此次测试 SQL> alter table d disable all triggers; Table altered. SQL> alter table e disable all triggers; Table altered. ----创建个视图 CREATE OR REPLACE VIEW V1 AS SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO; View created. SQL> select * from v1; EMPNO ENAME DEPTNO DNAME LOC ---------- ---------- ---------- -------------- ------------- 7369 SMITH 20 RESEARCH DALLAS 7499 ALLEN 30 SALES CHICAGO 7521 WARD 30 SALES CHICAGO 7566 JONES 20 RESEARCH DALLAS 7654 MARTIN 30 SALES CHICAGO 7698 BLAKE 30 SALES CHICAGO 7782 CLARK 10 ACCOUNTING NEW YORK 7788 SCOTT 20 RESEARCH DALLAS 7839 KING 10 ACCOUNTING NEW YORK 7844 TURNER 30 SALES CHICAGO 7876 ADAMS 20 RESEARCH DALLAS 7900 JAMES 30 SALES CHICAGO 7902 FORD 20 RESEARCH DALLAS 7934 MILLER 10 ACCOUNTING NEW YORK 14 rows selected. SQL> ----无法插入 因为这个视图涉及多个表连接 是复杂视图 SQL> insert into v1 values(8000,‘SEKER‘,50,‘JS‘,‘BJ‘); insert into v1 values(8000,‘SEKER‘,50,‘JS‘,‘BJ‘) * ERROR at line 1: ORA-01776: cannot modify more than one base table through a join view
利用替代触发器分解插入语句实现插入
insert into v1 values(8000,‘SEKER‘,50,‘JS‘,‘BJ‘);
分解这个语句到每个表
但在程序中我们应该使用 :new.XXX的值。
create or replace trigger tr_DML_v1 instead of insert on v1 begin insert into d(DEPTNO,DNAME,LOC) values(:new.DEPTNO,:new.DNAME,:new.LOC); insert into e(EMPNO,ENAME) values(:new.EMPNO,:new.ENAME); end; / Trigger created. SQL> insert into v1 values(8000,‘SEKER‘,50,‘JS‘,‘BJ‘); 1 row created. SQL> select * from d where deptno=50; DEPTNO DNAME LOC ---------- -------------- ------------- 50 JS BJ SQL> select * from e where empno=8000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 8000 SEKER SQL>
练习 1:DML触发器(作用于某个列上) --------------------------------------------------------------------------------- create or replace trigger e_update before update of sal on e for each row begin if updating then raise_application_error(-20001,‘salary do not modify!‘); end if; end; / 练习 2:DML触发器限制数据修改 --------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER secure_emp before INSERT ON e BEGIN IF TO_CHAR (SYSDATE, ‘HH24‘) NOT BETWEEN ‘08‘ AND ‘09‘ OR TO_CHAR (SYSDATE, ‘DY‘) IN (‘SAT‘, ‘SUN‘) THEN RAISE_APPLICATION_ERROR (-20205, ‘insert into table E！‘); END IF; END; / 练习 3:更新d表deptno时自动更新e表deptno --------------------------------------------------------------------------------- CREATE or replace TRIGGER vdate AFTER UPDATE OF deptno ON d FOR EACH ROW BEGIN IF (UPDATING AND :old.deptno != :new.deptno) THEN --关联标识（保留结构） UPDATE e SET deptno = :new.deptno WHERE deptno = :old.deptno; END IF; END; / 练习 4:删除d表数据时自动删除e表数据 --------------------------------------------------------------------------------- CREATE or replace TRIGGER vdate AFTER DELETE ON d