Oracle_触发器讲解.

触发器触发器类似于过程、函数，因为它们都是拥有说明部分、语句执行部分和异常处理部分的有名的PL/SQL块。与包类似，触发器必须存储在数据库中，并且不能被块进行本地化说明。但是，对于过程而言，可以从另一个块中通过过程调用显式地执行一个过程，同时在调用时可以传递参数。对于触发器而言，当触发事件发生时就会显式地执行该触发器，并且触发器不接受参数。触发事件是在数据库表上执行的DML（INSERT、UPDATE、DELETE）操作。使用触发器，可以做许多事情，包括：●维护不可能在表创建时通过说明性约束进行的复杂的完整性约束限制。●通过记录所进行的修改以及谁进行了修改来审计表中的信息。●当表被修改的时候，自动给需要执行操作的程序发信号。1．触发器概念及组成制作一个数据库触发器，目的是在某个表上执行特定的数据维护操作时，隐含地执行一个PL／SQL块。例子：制作一个数据库触发器。记录下那些超过其工种工资范围的工资值(但不禁止这种操作)CREATETRIGGERcheck_salBEFOREINSERTORUPDATEOFsal，jobONempFOREACHROWWHEN(new.job＜＞‘PRESIDENT’)DECLAERv_minsalsal_guide.minsal％TYPE；v_maxsalsal_guide.maxsal％TYPE；e_sal_out_of_rangeEXCEPTION；BEGINSELECTminsal，maxsalINTOv_minsal，v_maxsalFROMsal_guideWHEREjob＝：new.job；IF:new.sal＜v_minsalOR:new:sal＞v_maxsalTHENRAISEe_sal_out_of_range;ENDIF；EXCEPTIONWHENe_sal_out_of_rangeTHENINSERTINTOaudit_message(line_nr，line)VALUES(l,‘Salary’||TO_CHAR(:new.sal)||‘isoutofrangeforemployee’||TO_CHAR(:new.empno))；END；★关联触发器把一个数据库触发器的动作与另一个触发器联系起来，使之触发这另一个触发器。例子——制作相关联的触发器。在一个全局变量中统计审计信息的总数CREATETRIGGERcount_audit_messageBEFOREINSERTONaudit_messageBEGINemp_package.v_total_audit:=emp_package.v_total_audit+1；END；注释：·参数MAXOPENCURSORS限制在一个数据库中可用的关联触发器的个数，其缺省值为32。★写触发器的源代码之前，先确定好其触发时间、触发事件及触发器的类型。内容描述可能值触发时间与触发事件的时间次序●BEFORE●AFTER触发事件触发触发器的数据操作●INSERT事件类型●UPDATE●DELETE触发器类型触发器体被执行的次数●Statement●ROW触发器体该触发器将要执行的动作完整的PL／SQL块★行级触发器与语句级触发器的区别根据进行一个操作时触发器的触发次数，来决定是创建一个语句级触发器，还是创建一个行级触发器。●注意当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。例子：下面的语句对语句级触发器和行级触发器效果一样SQL＞INSERTINTODEPT(deptno，dname)VALUES(50，‘EDUCATION’)；SQL＞UPDATEDEPTSETLOC＝‘MAUI’WHEREDEPTNO＝50；SQL＞DELETEFROMDEPTWHEREDEPTNO＝50；●当触发事件影响数据库的多行时，语句级触发器只触发一次，而行级触发器则每一行被触发一次。例子----下列语句的行级触发器与语句级触发器效果不同SQL＞INSERTINTOEMP(empno，ename)2SELECTempno，ename3FROMEMP_BACKUP；SQL＞UPDATEDEPT2SETSAL＝SAL*1.13WHEREDEPTNO＝10；SQL＞DELETEFROMEMP2WHEREDEPTNO＝10；2．创建触发器⑴开发触发器的步骤●用系统编辑器或字处理软件写一个含有CREATETRIGGER语句的脚本文件。●在写CREATETRIGGER语句时，充分考虑运行时出错的处理。●在SQL*Plus或SVRMGR中运行脚本文件，将触发器的源代码编译成编译代码p_code，并把源代码存储到数据库中。编译代码p_code不存储在数据库中。●调试编译错误。●在ORACLE环境中测试开发的触发器（用INSERT、UPDATE、DELETE语句对表进行操作）。●在运行期间调试触发器的逻辑错误。⑵语句级触发器用CREATETRIGGBR语句创建一个语句级触发器，该触发器在一个数据操作语句发生时只触发一次。语法——创建一个语句级触发器CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER}event1[ORevent2...]ONtable_namePL/SQLblock其中:triggername触发器名timing指明触发事件触发的时间顺序：●BEFORE●AFTERevent指明触发事件的数据操纵类型●INSERT●UPDATE●DELETEtablename标明与该触发器相关联的表名pl／sqlblock触发器体，指明该触发器将执行的操作例子1——创建一个BEFORE型语句级触发器。限制一周内往EMP表插入数据的时间CREATEORREPLACETRIGGERsecure_empBEFOREINSERTONempBEGINIF(TO_CHAR(sysdate,‘DY’)IN(‘SAT’，‘SUN’))OR(TO_CHAR(sysdate,‘HH24’)NOTBETWEEN8AND18)THENRAISE_APPLICATION_ERROR(-20500，‘YOUmayonlyinsertintoEMPduringnormalhours．’);ENDIF；END；例子：创建一个包含多个触发事件的触发器。在触发器体中使用条件语句，指明INSERTING，UPDATING及DELETING，从而把多种触发事件组成一个触发器。对前面的例子进行扩展，使其不但限制某周内插入数据的时间，还限制进行数据修改和删除的时间。CREATETRIGGERsecure_empBEFOREDELETEORINSERTORUPDATEONempDECIAREv_dummyVARCHAR2(1)；BEGINIF(TO_CHAR(sysdate，‘DY’IN(‘SAT’，‘SHN’))OR(TO_NUMBER(sysdate，‘HH24’)NOTBETWEEN8AND18)THENIFDELETINGTHENRAISE_APPLICATION_ERROR(一20502，‘YoumayonlydeletefromEMPduringnormalhours.’)；ELSIFINSERTINGTHENRAISE_APPLICATION_ERROR(一20500，‘YoumayonlyinsertintoEMPduringmormalhours．’)；ELSIFUPDATING(‘SAL’)THENRAISE_APPLICATION_ERROR(一20503，‘YoumayonlyupdateSALcolumnduringnormalhours．’)；ELSERAISE_APPLICATION_ERROR(一20504，‘YoumayonlyupdateEMPtableduringnormalhours．’)；ENDIF；ENDIF；END；⑶行级触发器通过在CREATETRIGGER语句中指定FOREACHROW子句创建一个行级触发器，使其在受到触发事件影响的每一行上都被触发。★语法：创建一个行级触发器CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER}trigger_event1[ORtrigger_event2...]ONtable_name[FOREACHROW[WHENrestricting_condition]]PL/SQLblock;其中:restrictingcondition为指定的条件,以确定触发器体是否被执行。例子：创建一个行级触发器。记录下每个用户对数据库的表进行数据操纵的次数CREATETRIGGERaudit_empAFTERDELETEORINSERTORUPDATEONempFOREACHROWBEGINIFDELETINGTHENUPDATEaudit_tableSETdel＝del+lWHEREuser_name=userANDtable_name＝‘EMP’ANDcolun_nameISNULL；ELSIFINSERTINGTHENUPDATEaudit_tableSETins=ins+lWHEREuser_name=userANDtable_name=‘EMP’ANDcolumn_nameISNULL；ELSIFUPDATING(sal)THENUPDATEaudit_tableSETupd=upd+lWHEREuser_name＝user．ANDtable_name=‘EMP’ANDcolumnname=‘SAL’；EISEUPDATEaudit_tableSETupd=upd+1WHEREuser_name=userANDtable_name＝‘EMP’ANDcolumn_nameISNULL；ENDIF；END；注释:●如果一个触发器不必在触发事件前触发，请创建AFTER型触发器(不要创建BEFORE型)★行级触发器的标识符在行级触发器中，在列名前加上OLD标识符标识该列变化前的值，加上NEW标识符标识变化后的值。数据操作旧值新值InsertNULL插入的值Update修改前的值修改后的值Delete删除前的值NULL例子：在行级触发器中获取某列的新值和旧值。为EMP表中的所有数据保留一个历史档案（审计）CREATETRIGGERaudit_emp_valuesAFTERDELETEORINSERTORUPDATEONempFOREACHROWBEGININSERTINTOaudit_emp_values(user_name，timestamp，empno，old_ename，new_ename，old_job，new_job，old_mgr,new_mgr，old_sal，new_sal)VALUES(USER，SYSDATE，：old.empno，：old.ename，：new.ename，：old.job，：new.job，：old.mgr，：new.mgr，：old.sal，：new．sal)；END；详细的用户审计表用WHEN子句来保证，当某些行满足一定条件时，在该行上行级触发器才被触发。例子：在行级触发器限制其触发。自动计算销售员的奖金CREATETRIGGERderive_commBEFOREINSERTORUPDATEOFsalONempFOREACHROWWHEN（new.job＝‘SALESMAN’)BEGIN:new.comm：=：old.comm*(：new.sal／：old.sal)；END;注释:●在WHEN子句中，标识符前不加冒号(：)●在语句级触发器中不要使用WHEN子句。3．触发器的管理管理触发器的命令与管理过程的命令相似。任务命令___________________________________________________创建一个新的触发器CREATETRIGGER修改一个已有的触发器CREATEORREPLACETRIGGER删除触发器DROPTRIGGER与过程不同的是，当一个