文章目录
语句级触发器(表级别触发器)
触发器基本概念:
触发器是由一个事件来触发启动运行。即触发器是当某个事件发生时自动第隐式运行,并且触发器不能接受参数。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表示要被删除的数据。