文章目录

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

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

触发器基本概念：

触发器是由一个事件来触发启动运行。即触发器是当某个事件发生时自动第隐式运行，并且触发器不能接受参数。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表示要被删除的数据。