Oracle-Trigger-DML-1

SQL

DML触发器

DML触发器主要由DML语句进行触发，当用户执行了增加（INSERT）、修改（UPDATE）、删除（DELETE）操作的时候，就会触发操作。

DML 触发器创建语法

CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE | AFTER]
[INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] |DELETE] ON 表名称
[FOR EACH ROW]
[DISABLE]
[WHEN 触发条件]
[DECLARE]
[程序声明部分;]
[PRAGMA AUTONOMOUS_TRANSACTION;]
BEGIN
    程序代码部分;
END [触发器名称];
/

DML 触发器分为两类

表级触发器、行级触发器

操作顺序

当用户执行更新操作时，触发器的执行顺序如下：

BEFORE表级触发器执行；
BEFORE行级触发器执行；
执行更新操作；
AFTER行级触发器执行；
AFTER表级触发器执行；

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

表级触发器

表级触发器指的是针对于全表数据的检查，每次更新数据时，指挥在更新之前或之后触发一次，表级触发器不需要配置 “FOR EACH ROW” 选项。

范例 EMP 表创建

DROP TABLE EMP;
CREATE TABLE EMP (
    EMPNO NUMBER(10),
    ENAME VARCHAR2(20),
    SAL NUMBER(10,2),
    COMM NUMBER(10,2)
);
DROP TABLE EMP_TAX;
CREATE TABLE EMP_TAX (
    EMPNO NUMBER(10),
    ENAME VARCHAR2(20),
    SAL NUMBER(10,2),
    COMM NUMBER(10,2),
    TAX NUMBER(10,2)
);

范例 1：以下定义了一个触发器，周一、周六、周日无法更新表，09:00-18:00 可以进行表的更新操作。

CREATE OR REPLACE TRIGGER forbid_emp_trigger
BEFORE INSERT OR DELETE OR UPDATE
ON emp
DECLARE
   v_currentweak  VARCHAR2(20) ;
   v_currenthour VARCHAR2(20) ;
BEGIN
    SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh24') INTO v_currentweak,v_currenthour FROM dual;
  	IF TRIM(v_currentweak) = 'monday' OR TRIM(v_currentweak) = '星期一'
    OR TRIM(v_currentweak) = 'saturday' OR TRIM(v_currentweak) = '星期六'
    OR TRIM(v_currentweak) = 'sunday' OR TRIM(v_currentweak) = '星期天' THEN
    	RAISE_APPLICATION_ERROR(-20008,'EVERY WEAK MON,SAT,SUN CAN NOT DO IT');
  	ELSIF TRIM(v_currenthour) < '09' OR TRIM(v_currenthour) > '18' THEN
   		RAISE_APPLICATION_ERROR(-20009,'EVERY DAY 09:00-18:00 CAN  DO IT');
  	ELSE
    	null;    
  	END IF;
END;
/

范例 2：每天十二点以后不能修改 佣金、补助

CREATE OR REPLACE TRIGGER forbid_emp_trigger
BEFORE UPDATE OF sal,comm
ON emp
DECLARE
   v_currenthour VARCHAR2(20) ;
BEGIN
    SELECT TO_CHAR(SYSDATE,'hh24') INTO v_currenthour FROM dual;
    IF TRIM(v_currenthour) > '11' THEN
        RAISE_APPLICATION_ERROR(-20008,'after 12:00 you can not update sal ,comm');   
    END IF;
END;
/

范例 3：创建一个税收表，当更新 sal、comm 会触发税收表

CREATE OR REPLACE TRIGGER forbid_emp_trigger
AFTER INSERT OR UPDATE OF ename , sal , comm
ON emp
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   CURSOR cur_emp is select * from emp;
   v_empRow       emp%ROWTYPE;
   v_salary       emp.sal%TYPE;
   v_empTax       emp_tax.tax%TYPE;
BEGIN
  DELETE FROM 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;
    ELSIF v_salary BETWEEN 2000 AND 5000 THEN
      v_empTax := v_salary *0.10;
    ELSIF v_salary > 5000 THEN
      v_empTax := v_salary *0.20;    
    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;
/

行级的触发器

在之前所讲解的触发器操作是在对整张表进行 DML 操作之前或之后才进行的触发操作，并且只在更新前或更新后触发一次，而行级出发器指的是表种每一行记录出现更新操作时进行的触发操作，即：如果某些更新操作影响了多行数据，则每行数据更新时都会引起触发器操作，而如果要使用行级触发器，在定义触发器时必须定义 “FOR EACH ROW” 。

“:old.字段” 和 “:new.字段” 标识符

在使用行级触发器操作的过程之中，可以在触发器内部访问正在处理种的行数据，此时可以通过两个相关的标识符： “:old.字段” 和 “:new.字段” 实现的，而这两个表时符仅仅是在 DML 触发表种字段时才会有效

触发语句 :old.字段 :new.字段
INSERT 未定义，字段内容均为NULL INSERT操作结束后，为增加数据值
UPDATE 更新数据前的原始值 UPDATE操作之后，更新数据后的新值
DELETE 删除前的原始值 未定义，字段内容均为NULL

范例 EMP 表

DROP TABLE EMP;
CREATE TABLE EMP (
    EMPNO NUMBER(10),
    ENAME VARCHAR2(20),
    SAL NUMBER(10,2),
    COMM NUMBER(10,2),
    JOB VARCHAR2(10),
    DEPTNO NUMBER(10)
);

范例 1：增加雇员信息时，其职位必须在已有职位之内选择，并且工资不能超过 5000 ，用到 :new

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,'Add job infromation error');
    ELSE
    IF :new.sal > 5000 THEN
  		RAISE_APPLICATION_ERROR(-20008,'Add SAL IS MORE THAN $5000');
    END IF;
  END IF;
END;
/

范例 2：工资改动幅度不能超过 10% ,用到 :old

CREATE OR REPLACE TRIGGER forbid_emp_trigger
BEFORE UPDATE
ON EMP
FOR EACH ROW
DECLARE
BEGIN
    IF ABS((:new.sal - :old.sal) / :old.sal) >0.1 THEN
        RAISE_APPLICATION_ERROR(20008,'sal is too large');
  	END IF;
END;
/

范例 3：不能删除所有 10 部门的雇员

CREATE OR REPLACE TRIGGER forbid_emp_trigger
BEFORE DELETE
ON EMP
FOR EACH ROW
DECLARE
BEGIN
    IF :old.deptno = 10 THEN
  		RAISE_APPLICATION_ERROR(20008,'deptno 10 is not delete');
  	END IF;
END;
/

REFERENCING子句

如果现在觉得使用 “:new.字段” 或者 “:old.字段” 标记不清，那么可以通过 REFERENCING 子句为这两个标识符设置别名，例如可以将 “:new” 设置为 emp_new，或者将 “:old” 设置为：emp_old。

范例 1：

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,'Sal is so large');
    END IF;
END;
/

测试

INSERT INTO EMP (EMPNO,ENAME,SAL) VALUES (1,'zhangsan',100);
UPDATE EMP SET SAL = 200 WHERE EMPNO = 1;

WHEN 子句

在触发器定义语法之中也存在了 WHEN 子句，WHEN 子句是在触发器被触发之后，用来控制触发器是否被执行的一个控制条件，在 WHEN 子句之中也可以利用” new” 和 “old” 访问修改之后的数据，同时最方便的地方在于，WHEN 子句之中使用 “new” 和 “old” 时，可以不加前面的 “:”

范例 1：

CREATE OR REPLACE TRIGGER emp_insert_trigger
BEFORE INSERT
ON emp
FOR EACH ROW
WHEN (new.sal = 0)
BEGIN
    RAISE_APPLICATION_ERROR(-20008,'Sal is 0, ERROR' ||:new.empno || ','||:new.sal );
END;
/

测试

INSERT INTO EMP (EMPNO,ENAME,SAL) VALUES (1,'zhangsan',0);

触发器的谓词

除了依靠不同的操作时间来定义触发器外，也可以在一个触发器之中针对于一个触发器的不同状态来执行不同的操作，而此时为了区分出不同的DML操作，在触发器定义种专门提供了三个触发器谓词：

INSERTING、UPDATING、DELETEING

触发器谓词

触发器谓词 描述
INSERTING 如果触发器语句为 INSERT，返回 TRUE，否则返回 FALSE
UPDATING 如果触发语句为 UPDATE，返回 TRUE,否则返回 FALSE
DELETING 如果触发语句为 DELETE，返回 TRUE,否则返回 FALSE

范例 1：

创建表和序列

DROP TABLE dept_log purge;
DROP TABLE dept purge;
DROP SEQUENCE dept_log_seq;
CREATE SEQUENCE dept_log_seq;
CREATE TABLE dept(
    deptno 	NUMBER(2),
    dname 	VARCHAR2(14),
    loc 	VARCHAR2(13)
);
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)
);

创建触发器

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

FOLLOWS 子句

如果为一个表创建了多个触发器，那么其在进行触发时，是不会按照用户希望的触发顺序执行触发器的，假设用户希望触发器的执行顺序是：emp_insert_one、emp_insert_two、emp_insert_three，但是在默认情况下，各个触发器执行的顺序往往并不会像预期的那样。

范例 1：

CREATE OR REPLACE TRIGGER emp_insert_one
BEFORE INSERT
ON emp
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line('emp_insert_one');
END;
/

CREATE OR REPLACE TRIGGER emp_insert_two
BEFORE INSERT
ON emp
FOR EACH ROW
FOLLOWS emp_insert_one
BEGIN
  DBMS_OUTPUT.put_line('emp_insert_two');
END;
/

CREATE OR REPLACE TRIGGER emp_insert_three
BEFORE INSERT
ON emp
FOR EACH ROW
FOLLOWS emp_insert_two
BEGIN
  DBMS_OUTPUT.put_line('emp_insert_three');
END;
/

设置完后，多个触发器就会按照用户的需要进行顺序执行

Title:Oracle-Trigger-DML-1

Author:

Created:2022-02-28, 11:06:37

Updated:2022-11-18, 09:11:43

Full URL:http://yoursite.com/2022/02/28/Oracle-Trigger-DML-1/

License: "CC BY-NC-SA 4.0" Keep Link & Author if Distribute.

Contents
  1. 1. DML触发器
    1. 1.1. DML 触发器创建语法
    2. 1.2. 表级触发器
    3. 1.3. 行级的触发器
      1. 1.3.1. “:old.字段” 和 “:new.字段” 标识符
    4. 1.4. REFERENCING子句
    5. 1.5. WHEN 子句
    6. 1.6. 触发器的谓词
      1. 1.6.1. 触发器谓词
    7. 1.7. FOLLOWS 子句
|