知识点的梳理：

  • Oracle中的触发器分为DML触发器，instead-of（替代）触发器，DDL触发器，系统触发器和数据库事件触发器；
  • DML 触发器中分为以下两类：
    • 表级触发器：所有更新操作只在之前或之后触发一次；
    • 行级触发器：针对更新的每一行分别进行之前或之后触发；
  • 行级触发器中可以使用":old"取得更新前的数据，使用":new"取得更新后的数据；
  • 复合触发器是在Oracle11G之后增加的新功能，可以进行4个触发事件操作；
  • 如果要对视图进行更新操作，则应该使用替代触发器来完成，在替代触发器中，可以对视图中包含的多个数据表进行更新操作；
  • 当需要对发生的DDL操作进行触发时，可以采用DDL触发器。DDL触发器可以针对一个用户或整个数据库，如果针对数据库级应该具备管理员权限；
  • 每一个触发器只能编写最多32KB 的代码，当程序复杂时，可通过过程或函数进行功能切割；

      

  • 触发器简介
    • 触发器类似于过程和函数；
      • 例：当对某一张表执行更新操作（insert,update,delete）时，都可能引发触发器执行；
    • 触发器依靠事件执行；
    • 采用隐式调用，不能接收参数
  • 基本语法与分类
    • Oracle中的触发器分类：DML，instead-of(替代),DDL,系统或数据库事件触发器
    • 所有触发器都支持的基本创建语法：
<colgroup> </colgroup>

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]：替代触发器，对于视图操作所定义的触发器类型；

[INSERT | UPDATE |UPDATE OF 列名称 [,列名称,...]|DELETE]：触发的事件，可以是数据表的增加，修改，删除，或者部分字段的更新；
ON [表名称 | 视图 |DATABASE | SCHEMA] ：触发器的触发对象，可以是数据表，视图，数据库，模式（用户）；
[REFERENCING [OLD AS 标记] [NEW AS 标记] [PARENT AS 标记]]：对于":old",":new"，":parent"这3个标识符定义别名；
[FOR EACH ROW]：定义行级触发，没有此语句就是表级触发器；

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

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

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

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

BEGIN：程序主体部分；

END：触发器结束标记；

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

CREATE ANY TRIGGER

为任意用户创建触发器的权限

ALTER ANY TRIGGER

修改任意触发器的权限

DROP ANY TRIGGER

删除任意触发器的权限



DML触发器(53)

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

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

其中，[FOR EACH ROW] 选项最重要，该选项可以将DML触发器分为两类，表级触发器与行级触发器
  • 如果在一张表中定义了多个触发器，触发器的整体执行流程为：
<colgroup> </colgroup>

流程图，执行顺序分析：

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声明是在更新前触发
      • 代码：
<colgroup> </colgroup>

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表之中增加新雇员信息，执行触发器
<colgroup> </colgroup>

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表的增加，修改，删除操作的禁止；
      • 如果在非指定时间段上出现更新操作，就由用户抛出一个异常；
    • 代码：
<colgroup> </colgroup>

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 ;

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

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点以后，如果是就直接抛出一个异常
    • 代码：
<colgroup> </colgroup>

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字段
<colgroup> </colgroup>

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表
<colgroup> </colgroup>

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

) ;
  • 编写触发器
<colgroup> </colgroup>

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表记录
<colgroup> </colgroup>

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

VALUES (9898,'MLDN','MANAGER',SYSDATE,2000,500,7369,40) ;
  • 查询表，验证正确性
<colgroup> </colgroup>

SELECT * FROM emp_tax ;

  

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

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 IF ;

END ;

/
  • 插入错误的数据
<colgroup> </colgroup>

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()函数计算出数据的绝对值；
    • 代码：
<colgroup> </colgroup>

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
<colgroup> </colgroup>

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部门数据，则抛出一个删除数据的异常提示信息；
    • 代码：
<colgroup> </colgroup>

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部门）
<colgroup> </colgroup>

DELETE FROM emp WHERE empno=7839 ;
  • 示例4：":old.字段"和":new.字段"是不能被设置为ROWTYPE类型的，下面为错误示例
    • 错误的使用标识符":new"和":old"
<colgroup> </colgroup>

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数据
<colgroup> </colgroup>

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表中，同时删除子表中插入的数据，操作形式如下图：

        • 流程结构如下图：
    • 代码：数据库创建脚本
<colgroup> </colgroup>

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"数据
<colgroup> </colgroup>

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表中执行增加数据操作
<colgroup> </colgroup>

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

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

COMMIT ;
  • 查询member表数据
<colgroup> </colgroup>

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

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子句设置别名（修改雇员工资涨副触发器）
<colgroup> </colgroup>

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"标识符获取增加的雇员工资
<colgroup> </colgroup>

CREATE OR REPLACE TRIGGER emp_insert_trigger

BEFORE INSERT

ON emp

FOR EACH ROW

WHEN (new.sal = 0)

BEGIN

RAISE_APPLICATION_ERROR(-20008,:new.empno || '的工资为0，不符合工资规定！') ;

END;

/
  • 增加新雇员，工资为0 —— 不符合操作要求
<colgroup> </colgroup>

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

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

运行效果：

错误报告:

SQL 错误: ORA-20008: 8998的工资为0，不符合工资规定！

ORA-06512: 在 "SS.EMP_INSERT_TRIGGER", line 2

ORA-04088: 触发器 'SS.EMP_INSERT_TRIGGER' 执行过程中出错
  • 示例2：要求工资只能上涨，不能降低
    • 分析：如果要判断是否上涨，只需要判断新工资（:new.sal）是否大于原始工资（:old.sal）即可，直接在WHEN子句中判断
<colgroup> </colgroup>

CREATE OR REPLACE TRIGGER emp_sal_update_trigger

BEFORE UPDATE

ON emp

FOR EACH ROW

WHEN (new.sal<old.sal)

BEGIN

RAISE_APPLICATION_ERROR(-20008,:old.empno || '的工资少于其原本工资，无法更新！') ;

END;

/
  • 将7369的工资修改为300（原本为800，现在修改为300，属于降低工资，满足触发条件）
<colgroup> </colgroup>

UPDATE emp SET sal=300 WHERE empno=7369 ;
  • 触发器谓词
    • 除了依靠不同的操作事件来定义触发器外，还可以在一个触发器中对于触发器的不同状态来执行不同的操作；
    • 为了区分出不同的DML操作，在触发器定义中专门提供了3个触发器谓词，分别是INSERTING,UPDATEING,DELETING，它们的含义如下：
<colgroup> </colgroup>

  • 示例1：验证以上的触发器谓词。我们要对dept表执行一个操作日志的功能，当用户对dept表执行增加，修改，删除3个操作时，会自动在一个dept_log表中进行相关记录的保存
    • 定义DEPT_LOG表
<colgroup> </colgroup>

DROP TABLE dept_log PURGE ;

DROP SEQUENCE dept_log_seq ;

CREATE SEQUENCE dept_log_seq ;

CREATE TABLE dept_log (

logid NUMBER ,

type VARCHAR2(20) NOT NULL ,

deptno NUMBER(2) ,

logdate DATE ,

dname VARCHAR2(14) NOT NULL ,

loc VARCHAR2(13) NOT NULL ,

CONSTRAINT pk_logid PRIMARY KEY (logid)

) ;
  • 定义触发器，针对于不同的DML操作进行日志记录
<colgroup> </colgroup>

CREATE OR REPLACE TRIGGER dept_update_trigger

BEFORE INSERT OR UPDATE OR DELETE

ON dept

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'INSERT',SYSDATE,:new.deptno,:new.dname,:new.loc) ;

ELSIF UPDATING THEN

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'UPDATE',SYSDATE,:new.deptno,:new.dname,:new.loc) ;

ELSE -- 相当于DELETING

INSERT INTO dept_log (logid,type,logdate,deptno,dname,loc)

VALUES (dept_log_seq.nextval,'DELETE',SYSDATE,:old.deptno,:old.dname,:old.loc) ;

END IF ;

END;

/
  • 本触发器对于所有的DML事件都可以作出反应，在触发器程序内部，会使用3个触发器谓词对不同的操作进行判断，当满足某一操作时，会自动在dept_log数据表中保存操纵日志信息。另外，对于删除操作，不能使用":new"标识符操作，只能利用":old"标识符操作；
<colgroup> </colgroup>

INSERT INTO dept(deptno,dname,loc) VALUES (50,'MLDN','北京') ;

INSERT INTO dept(deptno,dname,loc) VALUES (60,'教学部','天津') ;

UPDATE dept SET dname='北京' WHERE deptno=60 ;

UPDATE dept SET dname='MLDNJAVA' WHERE deptno=50 ;

DELETE FROM dept WHERE deptno=60 ;

COMMIT ;
  • 更新完毕，查询dept_log表
<colgroup> </colgroup>

SELECT * FROM dept_log ;