Oracle12C--触发器（52）

最新推荐文章于 2022-04-25 11:56:36 发布

萧曵丶

最新推荐文章于 2022-04-25 11:56:36 发布

阅读量1k

点赞数 1

分类专栏： Orcale 文章标签：触发器

Orcale 专栏收录该内容

26 篇文章 5 订阅

订阅专栏

知识点的梳理：

Oracle中的触发器分为DML触发器，instead-of（替代）触发器，DDL触发器，系统触发器和数据库事件触发器；
DML 触发器中分为以下两类：
- 表级触发器：所有更新操作只在之前或之后触发一次；
- 行级触发器：针对更新的每一行分别进行之前或之后触发；
行级触发器中可以使用":old"取得更新前的数据，使用":new"取得更新后的数据；
复合触发器是在Oracle11G之后增加的新功能，可以进行4个触发事件操作；
如果要对视图进行更新操作，则应该使用替代触发器来完成，在替代触发器中，可以对视图中包含的多个数据表进行更新操作；
当需要对发生的DDL操作进行触发时，可以采用DDL触发器。DDL触发器可以针对一个用户或整个数据库，如果针对数据库级应该具备管理员权限；
每一个触发器只能编写最多32KB 的代码，当程序复杂时，可通过过程或函数进行功能切割；
触发器简介
- 触发器类似于过程和函数；
  - 例：当对某一张表执行更新操作（insert,update,delete）时，都可能引发触发器执行；
- 触发器依靠事件执行；
- 采用隐式调用，不能接收参数
基本语法与分类
- Oracle中的触发器分类：DML，instead-of(替代),DDL,系统或数据库事件触发器
- 所有触发器都支持的基本创建语法：

CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER] --触发时间
[INSTEAD OF]
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE] --触发事件
ON [表名称 | 视图 |DATABASE | SCHEMA] --触发对象
[REFERENCING [OLD AS 标记] [NEW AS标记] [PARENT AS 标记]]
[FOR EACH ROW] --触发频率
[FOLLOWS 触发器名称]
[DISABLE]
[WHEN 触发条件] --触发条件
[DECLARE] --触发操作（程序主体）
[程序声明部分;]
[PRAGMA AUTONOMOUS_TRANSACTION;]
BEGIN
程序代码部分;
END [触发器名称];
/

语法作用：
CREATE [OR REPLACE] TRIGGER 触发器名称：创建一个触发器，设置名称，如果选择了OP REPLACE选项，则表示替换已有的触发器；
[BEFORE | AFTER] ：该触发器的触发时间，是在操作之前（BEFORE）还是操作之后（AFTER）触发；

[INSTEAD OF]：替代触发器，对于视图操作所定义的触发器类型；

[FOLLOWS 触发器名称]：配置多个触发器执行的先后顺序；

[DISABLE]：触发器建立之后默认是启用状态，通过此选项，可将其定义为禁用状态；

[WHEN 触发条件]：当满足指定条件时才执行触发器操作；
[DECLARE]：触发器主体程序声明部分，定义变量或游标；

[PRAGMA AUTONOMOUS_TRANSACTION;]：自治事务声明，编写此语句后会在触发器中启动一个子事务处理，并且可以使用COMMIT提交事务；

BEGIN：程序主体部分；

END：触发器结束标记；

/：完结标记

触发器注意事项
- 触发器不接受任何参数，且只能是在产生了某一个触发事件之后才会自动调用；
- 对于一张数据表的触发器，最多只有12个，同一种类型的触发器，只能定义一次；
- 一个触发器最大为32KB，所以如果需要编写的代码较多，可以通过过程或函数调用来完成；
- 默认情况下，触发器中不能使用事务处理操作，或采用自治事务进行处理；
- 在一张数据表中，定义过多的触发器，会造成DML性能下降；
触发器相关权限

CREATE ANY TRIGGER	为任意用户创建触发器的权限
ALTER ANY TRIGGER	修改任意触发器的权限
DROP ANY TRIGGER	删除任意触发器的权限

DML触发器(53)

简介与创建语法
- 该触发器主要由DML语句触发，当用户执行了更新操作，就会触发；
- 语法：

CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER] --触发时间
[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE] ON 表名称
[FOR EACH ROW] --触发频率
[DISABLE]
[WHEN 触发条件] --触发条件
[DECLARE] --触发操作（程序主体）
[程序声明部分;]
BEGIN
程序代码部分;
END [触发器名称];
/

其中，[FOR EACH ROW] 选项最重要，该选项可以将DML触发器分为两类，表级触发器与行级触发器

如果在一张表中定义了多个触发器，触发器的整体执行流程为：

流程图，执行顺序分析：

1.BEFORE表级触发器执行；

2.BEFORE行级触发器执行；

3.执行更新操作；

4.AFTER行级触发器执行；

5.AFTER表级触发器执行；

说明：
如果现在的触发器建立的是一个行级触发器，且一个触发器会影响到多行数据，则会在每一行上执行一次次触发器操作（按照BEFORE行级触发器执行，执行更新操作，AFTER行级触发器执行流程重复执行）；

表级DML触发器
- 定义：表级触发器是针对全部数据的检查，每次更新数据表时，只会在更新之前或之后触发一次，表级触发器不需要配置FOR EACH ROW选项；
- 示例1：利用触发器进行全表限制：只有在每个月的10日才允许办理新雇员入职与离职，其他时间不允许增加新雇员数据
  - 分析：
    - 取得今天的日期，要使用SYSDATE伪列完成，而取得月，可以依靠TO_CHAR()函数的dd标记来完成；
    - 入职对于emp表而言属于数据增加操作，而离职对于emp表属于数据删除操作，所以本触发器应该针对insert,delete操作进行触发；
    - 如果标记天数不是10，则表示更新违法，手工抛出一个异常信息，手工设置异常代码为-20008；
    - 本程序是在增加数据前进行触发，所以定义时应该使用DEFORE声明是在更新前触发
  - 代码：

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT OR DELETE

ON emp

DECLARE

v_currentdate VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'dd') INTO v_currentdate FROM dual ;

IF TRIM(v_currentdate)!='10' THEN

RAISE_APPLICATION_ERROR(-20008,'在每月的10号才允许办理入职手续！') ;

END IF ;

END ;

向emp表之中增加新雇员信息，执行触发器

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

运行结果：
SQL 错误: ORA-20008: 在每月的10号才允许办理入职手续！

ORA-06512: 在 "SS.FORBID_EMP_TRIGGER", line 6

ORA-04088: 触发器 'SS.FORBID_EMP_TRIGGER' 执行过程中出错

示例2：利用触发器进行全表限制：在星期一、周末以及每天下班时间（每天9:00以前、18:00以后）后不允许更新emp数据表
- 分析：
  - 利用SYSDATE取得禁止更新的操作时间，要取得指定的一周时间数，使用TO_CHAR()函数，利用day格式求出，想取出小数，可利用hh24格式求出；
  - 更新emp表是指对emp表的增加，修改，删除操作的禁止；
  - 如果在非指定时间段上出现更新操作，就由用户抛出一个异常；
- 代码：

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT OR DELETE OR UPDATE

ON emp

DECLARE

v_currentweak VARCHAR(20) ;

v_currenthour VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh24') INTO v_currentweak, v_currenthour FROM dual ;

IF TRIM(v_currentweak)='星期一' OR TRIM(v_currentweak)='星期六' OR TRIM(v_currentweak)='星期日' THEN

RAISE_APPLICATION_ERROR(-20008,'在周末及周一不允许更新emp数据表！') ;

ELSIF TRIM(v_currenthour)<'9' OR TRIM(v_currenthour)>'18' THEN

RAISE_APPLICATION_ERROR(-20009,'在下班时间不能够修改emp表数据！') ;

END IF ;

END ;

假设当前日期时间为周日，发出以下增加数据操作

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

运行结果：

错误报告:

SQL 错误: ORA-20008: 在周末及周一不允许更新emp数据表！

ORA-06512: 在 "SS.FORBID_EMP_TRIGGER", line 7

ORA-04088: 触发器 'SS.FORBID_EMP_TRIGGER' 执行过程中出错

示例3：利用触发器针对单个字段的更新限制：在每天12点以后，不允许修改雇员工资和佣金
- 分析：
  - 通过SYSDATE取出当前的小时数，然后判断是否在12点以后，如果是就直接抛出一个异常
- 代码：

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE UPDATE OF sal,comm

ON emp

DECLARE

v_currenthour VARCHAR(20) ;

BEGIN

SELECT TO_CHAR(SYSDATE,'hh24') INTO v_currenthour FROM dual ;

IF TRIM(v_currenthour)>'12' THEN

RAISE_APPLICATION_ERROR(-20009,'每天12点以后不允许更新雇员工资、佣金。') ;

END IF ;

END ;

当前时间已超过中午12点，更新emp表中的sal和comm字段

UPDATE emp SET sal=9000,comm=5000 WHERE empno=7369 ;

以上为更新前触发器的操作，下面为更新后触发器的操作。更新后触发器的开发主要是将BEFORE修改为AFTER

示例4：每一位雇员都要根据其收入上缴所得税，假设所得税的上缴原则为：2000以下上缴3%、2000 ~ 5000上缴8%、5000以上上缴10%，现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、佣金、上缴所得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
- 分析：
  - 先建立一张雇员上缴所得税的信息表emp_tax;
  - 每次修改雇员信息，都要记录所得税信息，可将原来的所得税信息删除，而后增加新的数据；
  - 所有上缴所得税的信息应该在信息更新完毕后再进行操作，此时应该选择触发时间为AFTER；
  - 由于要记录每一个雇员的信息，可以使用游标；
  - 为了保证所得税信息表的数据可以及时提交，应在此触发器中使用自治事务；
- 步骤：
  - 创建emp_tax表

DROP TABLE emp_tax PURGE ;

CREATE TABLE emp_tax (

empno NUMBER(4) ,

ename VARCHAR2(10) ,

sal NUMBER(7,2) ,

comm NUMBER(7,2) ,

tax NUMBER(7,2) ,

CONSTRAINT pk_empno PRIMARY KEY (empno) ,

CONSTRAINT fk_empno FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE

) ;

编写触发器

CREATE OR REPLACE TRIGGER forbid_emp_trigger

AFTER UPDATE OR INSERT OF ename , sal , comm

ON emp

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION ; -- 触发器自治事务

CURSOR cur_emp IS SELECT * FROM emp ; -- 定义游标，找到每行纪录

v_empRow emp%ROWTYPE ; -- 保存emp的每行纪录

v_salary emp.sal%TYPE ; -- 计算总收入

v_empTax emp_tax.tax%TYPE ; -- 保存税收的数值

BEGIN

DELETE FROM emp_tax ; -- 清空emp_tax表的纪录

FOR v_empRow IN cur_emp LOOP

v_salary := v_empRow.sal + NVL(v_empRow.comm , 0) ; -- 计算总工资

IF v_salary < 2000 THEN

v_empTax := v_salary * 0.03 ; -- 上缴3%的税

ELSIF v_salary BETWEEN 2000 AND 5000 THEN

v_empTax := v_salary * 0.08 ; -- 上缴8%的税

ELSIF v_salary > 5000 THEN

v_empTax := v_salary * 0.1 ; -- 上缴10%的税

END IF ;

INSERT INTO emp_tax(empno,ename,sal,comm,tax) VALUES

(v_empRow.empno , v_empRow.ename , v_empRow.sal , v_empRow.comm , v_empTax) ;

END LOOP ;

COMMIT ; -- 提交事务

END ;

说明：定义了显式游标，来更新emp表的全部数据。程序主体部分为了保存新的数据，先将emp_tax数据表清空，之后游标是在更新后触发，所以通过游标打开所有数据，并将相关数据记录到emp_tax数据表中；
默认情况下，触发器是和触发它的DML使用了同一个事务，如果DML操作事务提交了，那么触发器的事务也会提交，反之触发器也会和数据一起回滚。默认情况下在触发器中是无法编写COMMIT，ROLLBACK的事务操作。可以增加"PRAGMA AUTONOMOUS_TRANSACTION;"，使用自治事务

向emp表中增加一条新的记录，而后查询emp_tax表记录

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (9898,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;

查询表，验证正确性

SELECT * FROM emp_tax ;

行级DML触发器
- 定义：表中每行记录出现更新操作时进行的触发操作。如果更新操作影响了多个数据，则每行数据更新都会引发触发器；行级触发器需使用FOR EACH ROW来定义；
- ":old.字段"和":new.字段"标识符
  - 通过这两个标识符，可在触发器内部访问正在被处理的行数据；
  - 这两个标识符只有在DML触发表中字段时才有效；它们只对行级触发器生效；
  - 图示：
  - 示例1：增加雇员信息时，其职位必须在已有职位之内选择，并且工资不能超过5000
    - 分析：
      - 如果要执行增加数据的检查，则应该在增加操作之前触发，应使用BEFORE INSERT；
      - 需要使用":new.字段",在定义触发器时应该使用FOR EACH ROW定义行级触发
      - 可以使用IN操作符判断新雇员的职位是否在已增加的雇员职位中；
    - 代码：

CREATE OR REPLACE TRIGGER forbid_emp_trigger

BEFORE INSERT

ON EMP

FOR EACH ROW

DECLARE

v_jobCount NUMBER ;

BEGIN

SELECT COUNT(empno) INTO v_jobCount FROM emp WHERE :new.job IN (

SELECT DISTINCT job FROM emp) ;

IF v_jobCount = 0 THEN -- 没有此职位信息

RAISE_APPLICATION_ERROR(-20008,'增加雇员的职位信息名称错误！') ;

ELSE

IF :new.sal > 5000 THEN

RAISE_APPLICATION_ERROR(-20008,'增加雇员的工资不得超过5000！') ;

END IF ;

END ;

插入错误的数据

INSERT INTO emp (empno,ename,job,hiredate,sal,comm,mgr,deptno)

VALUES (8998,'MLDN','经理',SYSDATE,9000,500,7369,40) ;

运行效果：

错误报告:

SQL 错误: ORA-20008: 增加雇员的职位信息名称错误！

ORA-06512: 在 "SS.FORBID_EMP_TRIGGER", line 7

ORA-04088: 触发器 'SS.FORBID_EMP_TRIGGER' 执行过程中出错

示例2：修改emp表的基本工资涨幅不能超过10%
- 分析：
  - 需要使用":old.字段"和":new.字段"分别取得数据更新前及更新后的数据，在定义触发器时应使用FOR EACH ROW定义行级触发；
  - 要判断涨幅是否超过10%，可以使用"（更新后数据-更新前数据）/更新前数据"判断其值是否大于0.1，同时为了防止负数问题的发生，可以使用ABS()函数计算出数据的绝对值；
- 代码：

CREATE OR REPLACE TRIGGER emp_update_trigger

BEFORE UPDATE OF sal

ON emp

FOR EACH ROW

BEGIN

IF ABS((:new.sal-:old.sal)/:old.sal) > 0.1 THEN

RAISE_APPLICATION_ERROR(-20008,'雇员工资修改幅度太大！') ;

END IF ;

END;

将雇员编号是7369的雇员工资增长为5000

UPDATE emp SET sal=5000 WHERE empno=7369 ;

运行结果：

错误报告:

SQL 错误: ORA-20008: 雇员工资修改幅度太大！

ORA-06512: 在 "SS.EMP_UPDATE_TRIGGER", line 3

ORA-04088: 触发器 'SS.EMP_UPDATE_TRIGGER' 执行过程中出错

示例3：不能删除所有10部门的雇员
- 分析：
  - 此时需要在执行删除操作之前取得已有删除行的数据，所以要使用行级触发器；
  - 如果发现要删除的数据中包含10部门数据，则抛出一个删除数据的异常提示信息；
- 代码：

CREATE OR REPLACE TRIGGER emp_delete_trigger

BEFORE DELETE

ON emp

FOR EACH ROW

BEGIN

IF :old.deptno=10 THEN

RAISE_APPLICATION_ERROR(-20008,:old.empno || '为10部门雇员，无法删除此部门雇员！') ;

END IF ;

END;

删除雇员编号是7839的雇员信息（此雇员在10部门）

DELETE FROM emp WHERE empno=7839 ;

示例4：":old.字段"和":new.字段"是不能被设置为ROWTYPE类型的，下面为错误示例
- 错误的使用标识符":new"和":old"

CREATE OR REPLACE TRIGGER emp_error_trigger

BEFORE UPDATE

ON emp

FOR EACH ROW

DECLARE

v_empRow emp%ROWTYPE ;

BEGIN

v_empRow := :old ; -- 错误

END;

运行效果：

示例5：在":old.字段"和":new.字段"时，":old"标记的值不能被更改，":new"是可以修改的
- 错误的程序 —— 在触发器之中无法修改old数据

CREATE OR REPLACE TRIGGER emp_update_old_trigger

BEFORE UPDATE OF sal

ON emp

FOR EACH ROW

BEGIN

:old.sal := 100 ; -- 错误，无法修改old数据

END;

利用触发器实现数据列自动增长，因为12C提供自动序列，故此示例针对12C以外的Oracle版本
- 操作流程：
  - 用户发出一个增加数据的INSERT指令，但是此时不设置自动增长列的内容；
  - 定义一个增加前的触发器，在触发器中，修改":new"标识符对应的自动增长列的内容；
  - 由触发器发出一条INSERT语句，执行数据增加；
- 注意事项：
  - 假设需要操作的是member表，如将以上3步都编写在member表中，执行增加操作的话，会出现触发器自己调用自己情况，造成死循环，如下图：
  - 解决此类问题最方便的做法是触发器不直接针对member表触发，而是针对一张与member表结构完全相同的子表触发。通过通过子表执行增加操作，然后子表的触发器将这些增加的数据插入到member表中，同时删除子表中插入的数据，操作形式如下图：
    - 流程结构如下图：
- 代码：数据库创建脚本

DROP SEQUENCE member_sequence ;

DROP TABLE member PURGE ;

DROP TABLE membertemp PURGE ;

CREATE SEQUENCE member_sequence ;

CREATE TABLE member(

mid NUMBER ,

name VARCHAR2(30) ,

address VARCHAR2(50) ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE membertemp AS SELECT * FROM member WHERE 1=2 ;

在触发器之中修改":new"数据

CREATE OR REPLACE TRIGGER member_insert_trigger

BEFORE INSERT

ON membertemp

FOR EACH ROW

BEGIN

DELETE FROM membertemp ;

INSERT INTO member(mid,name,address) VALUES (member_sequence.NEXTVAL,:new.name ,:new.address ) ;

END;

向membertemp表中执行增加数据操作

INSERT INTO membertemp(name,address) VALUES ('Hey','中国') ;

INSERT INTO membertemp(name,address) VALUES ('妹子','BING') ;

COMMIT ;

查询member表数据

SELECT * FROM member ;

上面的代码是在触发器中单独编写了一条完整的insert语句，而在Oracle中，也可以利用ROWTYPE直接作为增加数据的值，同时直接在触发器中队ROWTYPE型数据进行操作；
- 换种方式实现本程序的触发器：

CREATE OR REPLACE TRIGGER member_insert_trigger

BEFORE INSERT

ON membertemp

FOR EACH ROW

DECLARE

v_memberRow member%ROWTYPE ;

BEGIN

DELETE FROM membertemp ;

SELECT member_sequence.NEXTVAL INTO :new.mid FROM dual ;

v_memberRow.mid := :new.mid ;

v_memberRow.name := :new.name ;

v_memberRow.address := :new.address ;

INSERT INTO member VALUES v_memberRow ;

END;

使用REFERENCING子句设置别名
- 通过REFERENCING子句为":old.字段"和":new.字段"设置别名
- 示例：通过REFERENCING子句设置别名（修改雇员工资涨副触发器）

CREATE OR REPLACE TRIGGER emp_insert_trigger

BEFORE UPDATE OF sal

ON emp

REFERENCING old AS emp_old new AS emp_new

FOR EACH ROW

BEGIN

IF ABS((:emp_new.sal-:emp_old.sal)/:emp_old.sal) > 0.1 THEN

RAISE_APPLICATION_ERROR(-20008,'雇员工资涨副太大！') ;

END IF ;

END;

使用WHEN子句定义触发条件
- WHEN子句可以控制触发器在触发后，是否可以被执行；
  - 在该子句中，还可以利用":old"和":new"访问修改前后的数据；
  - 在WHEN子句中使用"old"和"new"不需要加"："；
- 示例1：在增加雇员时，判断雇员工资是否存在，如果工资为0则报错
  - 分析：此程序可以利用when子句作为触发器的执行条件，同时利用"new"标识符获取增加的雇员工资