ora4_6(触发器、变异表)

1、Oracle触发器1 触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。 功能：1、允许/限制对表的修改2、自动生成派生列，比如自增字段3、强制数据一致性4、提供审计和日志记录5、防止无效的事务处理6、启用复杂的业务逻辑2demo 开始createtriggerbiufer_employees_department_idbeforeinsertorupdateofdepartment_idonemployeesreferencingoldasold_valuenewasnew_valueforeachrowwhen(new_value.department

2、_id80)begin:new_mission_pct:=0;end;3触发器的组成部分：1、触发器名称2、触发语句3、触发器限制4、触发操作4 1、触发器名称createtriggerbiufer_employees_department_id命名习惯：biufer（beforeinsertupdateforeachrow）employees表名department_id列名5 2、触发语句比如：表或视图上的dml语句ddl语句数据库关闭或启动,startupshutdown等等beforeinsertorupdateofdepartment_idonemployeesreferencing

3、oldasold_valuenewasnew_valueforeachrow6 说明：1、规定了对employees表进行insert的时候2、对employees表的department_id列进行update的时候 触发器会激活7 3、触发器限制when(new_value.department_id80) 限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。其中的new_value是代表更新之后的值。8 4、触发操作是触发器的主体begin:new_mission_pct:=0;end; 主体很简单，就是将更新后的commission_pct列置为0

4、9触发时机触发时机 在dml触发器中，根据触发时机不同，可以分为before和after,但是在触发过程中其顺序不同。 执行顺序为 1.before触发器触发器 2.约束检查约束检查 3.更新表更新表 4.after触发器触发器10列数据列数据11Bir触发器触发器可以在这里插入代码可以在这里插入代码该函数可以看到插入该函数可以看到插入语句中的值。可以执语句中的值。可以执行各种不同的任务行各种不同的任务重写列重写列拒绝事拒绝事务务采取行采取行动动Oracle约束实施约束实施Air触发器触发器可以在这里插入代码可以在这里插入代码该函数可以看到插入该函数可以看到插入语句中的值。可以执语句中的值。可

5、以执行各种不同的任务行各种不同的任务违反约束被拒绝违反约束被拒绝拒绝事拒绝事务务采取行采取行动动阶段阶段执行的任务执行的任务更新表更新表Before触发器是在约束之前执行的，通常触发器是在约束之前执行的，通常用于：用于： 设置或修改更新或插入的列值 检查复杂的安全规则，如限制时间 增强商业应用规则 可以通过触发器的逻辑潜在的引发一个异常来拒绝触发语句，则相对有效，因为是在约束前执行12After触发器最后执行，一般用于：触发器最后执行，一般用于： 用户信息的审计 导出数据的生成 远程数据的复制13 触发器类型：触发器类型：1、语句触发器2、行触发器3、insteadof触发器4、系统条件触发器

6、5、用户事件触发器14 1、 语句触发器语句触发器是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与insert、update、delete或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。15例子：例子： 需要对在表上进行dml操作的用户进行安全检查，看是否具有合适的特权。createtablefoo(anumber); createtriggerbiud_foobeforeinsertorupdateordeleteonfoobeginifusernotin(do

7、nny)thenraise_application_error(-20001,youdonthaveaccesstomodifythistable.);endif;end;即使sys，system用户也不能修改foo表16 对修改表的时间、人物进行日志记录。 1、建立试验表createtableemployees_copyasselect*fromemp 2、建立日志表createtableemployees_log(whovarchar2(30),whendate);17 3、在employees_copy表上建立语句触发器，在触发器中填充employees_log表。createorrep

8、lacetriggerbiud_employee_copybeforeinsertorupdateordeleteonemployees_copybegininsertintoemployees_log(who,when)values(user,sysdate);end;18 4、测试updateemployees_copysetsalary=salary*1.1; select*fromemployess_log;19 5、确定是哪个语句起作用？即是insert/update/delete中的哪一个触发了触发器？可以在触发器中使用inserting/updating/deleting条件谓词

9、，作判断：beginifinsertingthen-elsifupdatingthen-elsifdeletingthen-endif;end; ifupdating(col1)orupdating(col2)then-endif;20 1、修改日志表altertableemployees_logadd(actionvarchar2(20);212、修改触发器，以便记录语句类型。createorreplacetriggerbiud_employee_copybeforeinsertorupdateordeleteonemployees_copydeclarel_actionemployees_

10、log.action%type;beginifinsertingthenl_action:=insert;elsifupdatingthenl_action:=update;elsifdeletingthenl_action:=delete;elseraise_application_error(-20001,youshouldneverevergetthiserror.);endif;insertintoemployees_log(who,action,when)values(user,l_action,sysdate);end;22 3、测试insertintoemployees_copy

11、(employee_id,last_name,email,hire_date,job_id)values(12345,chen,donnyhotmail,sysdate,12); select*fromemployees_log updateemployees_copysetsalary=50000whereemployee_id=12345;23 2、 行触发器行触发器是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：1、定义语句中包含foreachrow子句2、在foreachrow触发器中，用户可以引用受到影响的行值。24 定义：createtriggerbiuf

12、er_employees_department_idbeforeinsertorupdateofdepartment_idonemployees_copyreferencingoldasold_valuenewasnew_valueforeachrowwhen(new_value.department_id80)begin:new_mission_pct:=0;end;25 referencing子句：执行dml语句之前的值的默认名称是:old,之后的值是:newinsert操作只有:newdelete操作只有:oldupdate操作两者都有 referencing子句只是将new和old重命

13、名为new_value和old_value，目的是避免混淆。比如操作一个名为new的表时。26 为主健生成自增序列号 droptablefoo;createtablefoo(idnumber,datavarchar2(20);createsequencefoo_seq; createorreplacetriggerbifer_foo_id_pkbeforeinsertonfooforeachrowbeginselectfoo_seq.nextvalinto:new.idfromdual;end;27语句与行级触发器的区别？语句与行级触发器的区别？行级触发器行级触发器语句级触发器语句级触发器被触

14、发语句影响的每一行所触发不管影响多少行，触发语句只执行一次若触发事件对行没有产生更改影响，则不触发即使触发事件不影响任何一行，触发器也触发都可指定before,after都可指定before,after触发器执行体可以读取行数据触发器执行体与行数据无关只受当前触发事件影响只受当前触发事件影响After行触发器锁定数据行不锁定数据行28同一表上触发器的优先级别同一表上触发器的优先级别 同一表上可以定义多个触发器，最多可以创建12种类型的触发器，但是其触发是有固定顺序的，其先后顺序为： 1.before语句级触发器语句级触发器 2.before行级触发器行级触发器 3.after行级触发器行级触发

15、器 4.after语句级触发器语句级触发器29 3、insteadof触发器更新视图 createorreplaceviewv_emp_dept asselectempno,ename,hiredate,sal,deptno,dnamefromemp,deptwhereemp.deptno=dept.deptno 尝试往v_emp_dept表插入数据时只能通过替代触发器来完成30 createorreplacetriggertr_v insteadofinsertonv_emp_dept foreachrow begin insertintodeptmentvalues(:new.deptno

16、,:new.dname); insertintoemp(empno,ename,hiredate,sal)values(:new.empno,:new.ename,:new.hiredate,:new.sal); end;31 替代触发器可用来操纵对视图的插入，修改和删除。 Before和after无法用于替代触发器 视图上的check约束对替代触发器无效，因此需要在触发器语句内加强相关约束 Dml触发器是在dml操作外运行的，而替代触发器则代替触发它的dml命令运行。本质上,替代触发器属于行级。32替代触发器的使用经验替代触发器的使用经验 4、系统事件触发器系统事件：数据库启动、关闭，服务器

17、错误 createtriggerad_startupafterstartupondatabasebegin-dosomestuffend;/33 5、用户事件触发器用户事件：用户登陆、注销，create/alter/drop/analyze/audit/grant/revoke/rename/truncate/logoff34 例子：记录删除对象 1.日志表createtabledroped_objects(object_namevarchar2(30),object_typevarchar2(30),dropped_ondate); 2触发器createorreplacetriggerlog

18、_drop_triggerbeforedropondonny.schemabegininsertintodroped_objectsvalues(ora_dict_obj_name,-与触发器相关的函数ora_dict_obj_type,sysdate);end;/35 3.测试createtabledrop_me(anumber);createviewdrop_me_viewasselect*fromdrop_me;dropviewdrop_me_view;droptabledrop_me; select*fromdroped_objects36 禁用和启用触发器altertriggerdi

19、sable;altertriggerenable; 事务处理：在触发器中，不能使用commit/rollback因为ddl语句具有隐式的commit，所以也不允许使用37触发器的限制 触发器的使用存在一些限制条件，需要引起高度重视！38 1.createtrigger语句文本字符长度不能超过32k，如果触发器的逻辑编码超过60行，则最好将主要代码保存到存储过程。使用时之需要从触发器中调用存储过程即可。 2.不要创建递归式触发器，否则，系统会因为触发器与表之间的循环操作而导致内存耗尽，系统崩溃。 3.触发器中不能使用数据库事务控制语句commit,rollback和savepoint 4.由触发

20、器所调用的过程或者函数也不能使用数据库事务控制语句。39 6.触发器中不能声明long,longraw类型变量，也不能在触发表的long或longraw列上使用:new和:old. 7.触发器体内可以读取Lob类型的列值，但不能通过：new修改lob列的数据。 8.when条件子句只适用于行级触发器，一旦指定了when，触发器只执行满足when子句条件的行40 考虑问题如下： 写一个ORACLE触发器,主要是解决一个系统的管理员帐号经常被培训的人修改的问题.功能是在Update用户表时如果是修改的管理员,则将管理员的密码改回到初始状态（假定初始密码是oldpassword),用户表是user(

21、id,password,job_title)41CREATE OR REPLACE TRIGGER before_update_password before update of password on new_users for each row when (new.jobtitle=admin)declare err exception;begindbms_output.put_line(trigger active);if :old.jobtitle=admin then raise err;end if;exception when err then raise_application

22、_error(-20009,pls do not change admin password);when others then dbms_output.put_line(sqlerrm);end;42 CREATEORREPLACETRIGGERbefore_update_passwordbeforeupdateofpasswordonnew_usersforeachrowwhen(new.jobtitle=admin) declare errexception; var_titlevarchar2(20); begin dbms_output.put_line(trigger active

23、); raiseerr; exception whenerrthen raise_application_error(-20009,plsdonotchangeadminpassword); whenothersthen dbms_output.put_line(sqlerrm); end;43练习题 1.createtabletemp(xnumber,ynumber,znumberdefault5) 编写一个仅在以下条件触发的触发器： 1）将y从null值变为一个notnull值的update触发器 2）x介于1-10之间的insert触发器要求显示究竟是什么条件触发的触发器。44一个较为完

24、整的例子45要求如下：1.部门名称限制在（math,hist,engl,scie）2.Tenure只能是yes或no3.薪水上限为300004.Department转换为大写格式5.Salary四舍五入6.对于任何insert，如果教授的薪水超过10000且部门是engl，则拒绝（注意，所有的工资都必须以30000为上限） 对以上规则考虑增加如下约束： 如果当前预算超过55000就不能增加教授 总的预算不能超过600004647PARKING_TICKETSTicket NoAmountStateTagNoP_015.00CACD2348P_025.00NYMH8709P_035.00NYMH

25、8709P_045.00NYJR9837The following scenario demonstrates a recursive delete. The delete row trigger may delete additional rows. It may not. It depends on the data. The business rule is:When a student pays a parking ticket, all other tickets for the same car and for the same amount are deleted as well

26、, provided the sum of the tickets to be deleted does not exceed $10.00.48触发器与变异表触发器与变异表变异表变异表:当前正在被当前正在被DML语句更新的表，对于语句更新的表，对于触发器而言，变异表就是在其上定义该触发器触发器而言，变异表就是在其上定义该触发器的那张表。的那张表。触发器中的触发器中的sql语句不能：（适用于所有行级触语句不能：（适用于所有行级触发器）发器）1.读取或更新触发语句的任何变异表，读取或更新触发语句的任何变异表，包括触发表本身。包括触发表本身。2.读取或更新该触发表的约束表的主键读取或更新该触发表的

27、约束表的主键列，惟一性健列或外健列列，惟一性健列或外健列思考如何理解第二个条件思考如何理解第二个条件49 给出两个表：简单概括起来是这样的：给出两个表：简单概括起来是这样的：1、主表、主表pater 。两个字段。两个字段:id number; -主键主键count number;-计数计数2、子表、子表sub。 两个字段两个字段:id number; -主键主键pid number;-外键。为外键。为pater的的id字段；字段；删除的时候级连删除删除的时候级连删除50 在在sub表上创建了两个触发器；分别在表上创建了两个触发器；分别在sub表插入和删除记录的表插入和删除记录的时候更新时候更新

28、pater表相应表相应id记录内的记录内的count计计数；数；单独对单独对sub插入插入/删除一切正常；删除一切正常；pater表表的计算也正确；的计算也正确；51可是在主表出现更新可是在主表出现更新时，出现问题！时，出现问题！隐藏的变异表问题隐藏的变异表问题 删除删除pater表内记录时；如果表内记录时；如果sub表内存表内存在子记录，级连删除时，在子记录，级连删除时，sub上的触发器上的触发器试图更新试图更新pater的的count；就遇到变异表问；就遇到变异表问题题52变异表示例变异表示例 问题：将每一门课程的选修人数限制在2人，在students表上定义一个beforeinserto

29、rupdate行级触发器实现 students（Id,major)53 create or replace trigger limitmajors before insert or update of major on students for each row declare v_maxstudents constant number:=2; v_currentstudents number; begin select count(*) into v_currentstudents from students where major=:new.major; if(v_currentstude

30、nts+1v_maxstudents) then raise_application_error(-20001,too many students major in |:new.major); end if; end;54创建测试环境创建测试环境 createtablestudents(idnumberprimarykey,majorvarchar2(20); insertintostudentsvalues(1,math) insertintostudentsvalues(2,math) 注意注意 insert into students values(3,math) 会发生什么？会发生什么

31、？55原因是什么？原因是什么？ 注意，如果只注意，如果只INSERT一行，那么该行的行前和行一行，那么该行的行前和行后触发器就不把触发表看做变异表，这也是行级触后触发器就不把触发表看做变异表，这也是行级触发器能够读取或修改触发表的唯一情况。发器能够读取或修改触发表的唯一情况。如如INSERT INTO TABLE SELECT .语句，触发语句，触发器仍然会把触发表当做变异表看待，即使插入一行器仍然会把触发表当做变异表看待，即使插入一行也不可以。也不可以。Update呢？呢？ 测试一下吧：测试一下吧：56用用insert .select 子句测试子句测试 create table studen

32、ts_copy as select * from students where 1=2; insert into students_copy values(3,math)； insert into students select * from students_copy57用update测试 insertintostudentsvalues(3,english); updatestudentssetmajor=mathwhereid=3;58 按照上面的写法，触发器查询了它自己按照上面的写法，触发器查询了它自己的触发表，而触发表又是变异表，所以的触发表，而触发表又是变异表，所以无法达到期待的结

33、果。无法达到期待的结果。 此类问题具有一定普遍性，如何修改呢此类问题具有一定普遍性，如何修改呢？59 修改思路如下：修改思路如下：将上面的触发器修改成将上面的触发器修改成2个触发器，行级个触发器，行级与语句级触发器与语句级触发器 1.在行级触发器中，可以记录在行级触发器中，可以记录 :new.major 的值，但不查询的值，但不查询students表表2.查询在语句级触发器中实现。查询在语句级触发器中实现。60需要需要 保存一些全局性变量以在两个触发器保存一些全局性变量以在两个触发器间传递变量，可以用包来实现：间传递变量，可以用包来实现： Create or replace package studentdata asType t_majors is table OF students.major%typeIndex by BINARY_INTEGER;Type t_IDs is table OF students.ID%typeIndex by BINARY_INTEGER;V