Oracle12C--DML触发器(五十三)

• 简介与创建语法
  • 该触发器主要由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点以后，不允许修改雇员工资和佣金
  • 分析