oracle触发器

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,本章大纲,触发器概述,使用原因,概念,分类,DML触发器,Instead of触发器,系统事件触发器,1,触发器概述,为什么要用触发器,实现企业自定义的业务规则，它比约束更为复杂和强大。,案例,不能在星期六、星期日更新员工信息。,更新员工工资，只能增加，不能减少。,添加员工信息，如果该部门员工人数超过3人，则不能再添加。,解决,使用触发器。,上面的业务规则，用普通的约束都是不能实现的！,2,触发器概述,概念,数据库特定事件发生时所自动执行的块。,用途,当表数据发生改变时，校验表(,主要用途,)。,根据数据库发生的事件来完成数据库维护工作。,事件分类(根据监控对象来分类),DML触发器：监控表,Instead of触发器：监控视图,数据库事件：监控数据库启动、登录、关闭等事件,工作中最常用，必须掌握！,3,DML触发器,概念,当表发生数据修改事件(增加、删除、修改)自动执行的PL/SQL 块。,语法,CREATE,OR,REPLACE,TRIGGER,触发器名称,AFTER,|,BEFORE,-指定触发时机,INSERT,|,DELETE,|,UPDATE,-指定触发事件类型,ON,表名,-指定所监控的表,FOR,EACH,ROW,|,FOR,EACH,STATEMENT,-指定触发次数,-上面：执行条件-下面：执行代码-,BEGIN,代码;,END,;,4,DML触发器,相关概念,AFTER,|,BEFORE,在什么事件之前或之后执行,区别:before能够修改:new变量，而after不能。,INSERT,|,DELETE,|,UPDATE,什么事件,ON,表名,触发器建在什么表上，即监控什么表,一个触发器只能关联一张表，而一张表上可以有多个触发器。,AFTER,|,BEFORE,-指定触发时机,INSERT,|,DELETE,|,UPDATE,-指定触发器事件,ON,表名,-指定所监控的表,FOR,EACH,ROW,|,FOR,EACH,STATEMENT,-指定触发器次数,5,DML触发器,FOR,EACH,ROW,行级触发,示例：delete from t1,删除1000行，则执行1000次(一行一次),FOR,EACH,STATEMENT,语句级触发,示例： delete from t1,删除1000行，则执行1次(一句一次),:new 行变量,保存事件发生时,新,数据所在行。,只有insert和update才有新数据。,:old 行变量,保存事件发生时,旧,数据所在行。,只有delete和update才有旧数据。,6,DML触发器,示例：分析事件中的新数据和旧数据,insert into emp(empno,ename) values(51,job);,分析：只有一行新数据(51,job)，对应:new变量。,update emp set ename=oracle where empno = 51;,分析：旧数据 (51,job) ，对应:old变量。,新数据(51,oracle)，对应:new变量。,delete from emp where empno = 51;,分析：只有一行旧数据(51,oracle),对应 :old变量。,小结,事件,:new,:old,insert,delete,update,7,DML触发器,案例1：,实现表t1(t_id,t_name)的自动编号。,分析：当表t1发生Insert事件之前自动执行。添加几行就执行几次。,知识点：理解触发器，使用:new关键字。,CREATE,OR,REPLACE,TRIGGER,tr_t1_autoid,BEFORE,INSERT,ON,t1,FOR,EACH,ROW,DECLARE,BEGIN,SELECT,seq_t1_id.,NEXTVAL,INTO,:,NEW,.t_id,FROM,dual;,END,演示,8,DML触发器,案例2：,更新员工时，工资只能增加，不能减少。,分析：当emp发生update事件时，检查前后工资，如果变少，则通过抛出异常中断更新事件。,知识点：使用:old和:new。,CREATE,OR,REPLACE,TRIGGER,tr_emp_update_sal,BEFORE,UPDATE,ON,emp,FOR,EACH,ROW,DECLARE,BEGIN,IF,(:,OLD,.sal:,NEW,.sal),THEN,raise_application_error(-20009,老板，工资只能加不能减！);,END,IF,;,END,;,演示,9,DML触发器,案例3：,更新员工时，工资只能增加，不能减少。删除员工时，不能删除项目经理(job=manager),知识点：在触发器中监控和判断多个事件。,IF,updating,THEN,IF,(:,OLD,.sal:,NEW,.sal),THEN,raise_application_error(-20009,老板，工资只能加不能减！,);,END,IF,;,END,IF,;,IF,deleting,THEN,IF,(:,OLD,.job=MANAGER),THEN,raise_application_error(-20009,不能删除项目经理！,);,END,IF,;,END,IF,;,END,;,演示,10,DML触发器,实例,案例4：添加员工信息，如果该部门员工人数超过3人，则不能再添加。,分析：当emp发生insert事件，检查新员工所在部门人数，如果超过3人，则通过抛出异常中断事务。,知识点：使用条件谓词,演示,11,DML触发器,实例,案例5：部门表的部门编号(deptno)更改时，该部门的员工的部门编号也自动修改。,CREATE or replace TRIGGER dept_update_cascade,AFTER UPDATE OF deptno ON dept,FOR EACH ROW,BEGIN,UPDATE emp,SET deptno = :new.deptno,WHERE deptno = :old.deptno;,END;,注意：经常在一个表变化时，可以在触发器中操作相关的其它表。,调用：,1,、没有直接的调用方式,2,、只要,dept,表的,deptno,有修改，则“,自动,”执行此,PL/SQL,块。,12,DML触发器,实例,案例6：阅读代码，新记录的t_id值是几？。,演示,第1步：INSERT INTO t1(t_id,t_name) VALUES(5,c);第2步：触发：tr_t1第3步：产生 :new行 t_id = 5 ,t_name = c第4步：执行代码 t_id =,3,t_name = c提交：最后的数据: t_id = 3 t_name = c,13,DML触发器,实例：演示before和after的区别,任务一、t1用最大编号+1 实现自动编号，名称转化为大写。,任务二、t1添加记录时，同时将记录添加到t2表。,要点：,任务一由于要修改:new行变量，所以必须用before。,任务二由于没有修改new行变量，所以before和after都可以。,小结：before能够修改:new变量，而after不能。,演示,14,DML触发器,最全的触发器(提示),before insert or update or delete,begin,if inserting then sp_t1_insert();end if;,if deleting then sp_t1_delete();end if;,if updating then sp_t1_update();end if;,end;,15,触发器应用,使用要点,只要在数据修改时需要自动完成一些工作，就可以使用触发器。,在大型系统中，更要慎重使用触发器。太多触发器很容易造成程序难以维护，甚至造成死循环。,使用场合,控制数据安全,实现数据审计,实现数据完整性,实现参照完整性,16,其它触发器,INSTEAD OF触发器,让不能更新的视图(如多表联接)能够完成“更新”,注意：简单的视图(只有一个表)本身是可以更新的。,系统事件触发器,在系统事件(启动，关闭)时自动执行的触发器,示例：实例启动与关闭,示例：系统登录及退出,DDL触发器,在创建和删除数据库对象执行的触发器,示例：记录scott模式下所有创建和删除对象信息。,17,管理触发器,显示触发器,禁止触发器,何时禁止：数据导入导出时禁止,激活触发器,重新编译触发器,数据库对象发生改变后,删除触发器,18,触发器VS存储过程,19,小结,触发器三大要点,执行事件,执行时机,执行次数,触发器的作用,无人值守,自动执行,触发器分类,针对表,针对视图,针对Oracle系统,针对模式(如：Scott),20,作业,根据自动编号实例，画出触发器的执行流程。,21,实训/作业,根据自动编号实例，画出触发器的执行流程。,实现“智能”自动编号(难度：),用户指定主键，则使用 用户的编号,用户不指定主键，则编号自动加一,考勤系统统计实现(难度： ),通过触发器实现员工每天打卡情况统计,通过存储过程实现员工每天打卡情况统计,22,