语句级触发器（表级别触发器）、行级触发器

爱吃鱼的俊懒猫 2020-03-25

展开全文

文章目录

语句级触发器（表级别触发器）
行级触发器

语句级触发器（表级别触发器）

触发器基本概念：

触发器是由一个事件来触发启动运行。即触发器是当某个事件发生时自动第隐式运行，并且触发器不能接受参数。oracle事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。
触发器分为语句级触发器（又叫做表级触发器）和行级触发器。

DEMO:在周末不能办理雇员的离职或者入职手续

CREATE OR REPLACE TRIGGER emp_trigger  ---创建触发器
  BEFORE INSERT OR DELETE  ---在增加或者删除数据之前触发
ON emp                     ---表示触发器建立在emp数据表上

---声明变量
DECLARE
  v_date VARCHAR2(20);
BEGIN
  SELECT TO_CHAR(SYSDATE,'day') INTO v_date FROM dual;
  IF v_date='星期六' OR v_date='星期日' THEN
  RAISE_APPLICATION_ERROR(-20001,'周末不能办理离职或者入职手续');
  END IF;
END;
/
---增加数据
INSERT INTO emp(empno,ename,job,sal)
  SELECT 1008,'xxx','总裁',960 FROM dual
  union
  SELECT 1009,'xxx','总裁',960 FROM dual
  union
  SELECT 1010,'xxx','总裁',960 FROM dual;
DELETE FROM emp WHERE empno=7788;

此时触发器已经创建

DEMO:周末以及下班时间不允许更新emp数据表

CREATE OR REPLACE TRIGGER emp_trigger
  BEFORE UPDATE
ON emp
DECLARE
  v_date VARCHAR2(50);
  v_hour VARCHAR2(50);

BEGIN
  SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh') INTO v_date,v_hour FROM dual;

  IF v_date='星期六' OR v_date='星期日' THEN
     ---抛出错误-20999到-20000之间
     RAISE_APPLICATION_ERROR(-20001,'周末不上班');
    ELSIF v_hour>18 OR v_hour<10 THEN
      RAISE_APPLICATION_ERROR(-20002,'现在是下班时间');
  END IF;
END;
/

---增加数据
UPDATE emp SET job='salesman' WHERE empno=7369;

以上创建的触发器是语句级触发器（表级触发器），语句级触发器对每个DML语句都触发一次，
如果一条INSERT语句在TABLE表中插入500行，那么这个表上的语句级触发器只执行一次，而行级触发器就要执行500次了。

DEMO:在每天下班后不能修改雇员的薪资和佣金

---创建触发器
CREATE OR REPLACE TRIGGER emp_trigger
  BEFORE UPDATE OF sal,comm  ---在修改薪资或佣金之前触发
ON emp                       ---表示触发器建立在emp数据表上

---声明变量
DECLARE
  v_date VARCHAR2(50);
  v_hour VARCHAR2(50);  ---保存时间
BEGIN 
  SELECT TO_CHAR(SYSDATE,'day'),TO_CHAR(SYSDATE,'hh') INTO v_date,v_hour FROM dual;
  IF v_date='星期六' OR v_date='星期日' THEN
      ---抛出错误
    RAISE_APPLICATION_ERROR(-20001,'周末不上班');
  ELSIF v_hour>18 OR v_hour<10 THEN
    RAISE_APPLICATION_ERROR(-20002,'现在是下班时间');
  ENDIF;
END;
/
UPDATE emp SET job='salesman' WHERE empno=7788;

行级触发器

表级别触发器（语句级触发器）的特征是就算执行多条sql语句也只会触发一次，而行级触发器对DML语句而言，只要有一条数据被影响就会触发一次。

DEMO:增加雇员信息的时候薪资范围在1000～5000之间

CREATE OR REPLACE TRIGGER emp_trigger
 BEFORE INSERT  ---表示在增加数据之前触发
ON emp          ---表示触发器建立在emp数据表上 
FOR EACH ROW    ---表示行级触发器
BEGIN
  IF :new.sal>5000 OR :new.sal<1000 THEN 
     RAISE_APPLICATION_ERROR(-20001,'薪资范围不合法');
  END IF;
END;
/

---插入数据
INSERT INTO emp(empno,ename,job,sal) VALUES(1008,'xxx','总裁',960);
INSERT INTO emp(empno,ename,job,sal) VALUES(1009,'xxx','总裁',960);
INSERT INTO emp(empno,ename,job,sal) VALUES(1011,'xxx','总裁',960);

FOR EACH ROW:表示该触发器是行级别的，如果不写表示语句级触发器（表级触发器）:new.sal:表示即将插入的数据中的sal字段的值，不能在表级触发器中出现

DEMO:薪资涨幅不能超过原来的百分之十

CREATE OR REPLACE TRIGGER emp_trigger
  BEFORE UPDATE OF sal  ---在修改薪资之前触发
ON emp
FOR EACH ROW
BEGIN 
    IF :new.sal>:old.sal*1.1 THEN
       RAISE_APPLICATION_ERROR(-20001,'薪资涨幅太大了');
    END IF;
END;
/
UPDATE emp SET sal=5000 WHERE empno=7369;

DEMO:更新了dept表的deptno字段之后对应的emp表也随之更新（级联更新）

主表和子表之间是可以进行级联操作的，但是在Oracle中不能实现级联更新，只能使用触发器完成。

CREATE OR REPLACE TRIGGER dept_trigger
  BEFORE UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;
/
UPDATE dept SET deptno=70 WHERE deptno=10;

此时就使用触发器在oracle中实现了级联更新操作

DEMO:不能删除职位是总裁的雇员信息

CREATE OR REPLACE TRIGGER emp_trigger
  BEFORE DELETE  ---表示删除emp表中的数据之前触发
ON emp
FOR EACH ROW
BEGIN
   IF :OLD.job='PRESIDENT' THEN
     RAISE_APPLICATION_ERROR(-20001,'不能删除该职位的雇员的信息');
END IF;
END;
/
DELETE FROM emp WHERE empno=7839;

总结：

1.行级触发器只会每条sql语句都会触发一次
2.实现行级触发器需要使用“FOR EACH ROW”关键字
3.:new和:old使用方法和意义，new和old只出现在insert和update的sql语句中，在insert中时new表示新插入的行数据，在update中时new表示要替换的新数据、old表示要被更改的原来的数据行，在delete中时old表示要被删除的数据。