ORACLE PLSQL编程之八-把触发器说透

ORACLEPL/SQL编程之八:把触发器说透摘自和讯网特性INSERTUPDATEDELETEOLDNULL实际值实际值NEW实际值实际值NULL例1:建立一个触发器,当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中去。CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2;CREATEORREPLACETRIGGERtr_del_empBEFOREDELETE指定触发时机为删除操作前触发ONscott.empFOREACHROW说明创建的是行级触发器BEGIN将修改前数据插入到日志记录表del_emp,以供监督使用。INSERTINTOemp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate)VALUES(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate);END;DELETEempWHEREempno=7788;DROPTABLEemp_his;DROPTRIGGERdel_emp;例2：限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围，即不允许在非工作时间修改departments表。CREATEORREPLACETRIGGERtr_dept_timeBEFOREINSERTORDELETEORUPDATEONdepartmentsBEGINIF(TO_CHAR(sysdate,DAY)IN(星期六,星期日))OR(TO_CHAR(sysdate,HH24:MI)NOTBETWEEN08:30AND18:00)THENRAISE_APPLICATION_ERROR(-20001,不是上班时间，不能修改departments表);ENDIF;END;例3：限定只对部门号为80的记录进行行触发器操作。CREATEORREPLACETRIGGERtr_emp_sal_commBEFOREUPDATEOFsalary,commission_pctORDELETEONHR.employeesFOREACHROWWHEN(old.department_id=80)BEGINCASEWHENUPDATING(salary)THENIF:NEW.salary:old.salaryTHENRAISE_APPLICATION_ERROR(-20001,部门80的人员的工资不能降);ENDIF;WHENUPDATING(commission_pct)THENIF:NEW.commission_pct:old.commission_pctTHENRAISE_APPLICATION_ERROR(-20002,部门80的人员的奖金不能降);ENDIF;WHENDELETINGTHENRAISE_APPLICATION_ERROR(-20003,不能删除部门80的人员记录);ENDCASE;END;/*实例：UPDATEemployeesSETsalary=8000WHEREemployee_id=177;DELETEFROMemployeesWHEREemployee_idin(177,170);*/例4：利用行触发器实现级联更新。在修改了主表regions中的region_id之后(AFTER)，级联的、自动的更新子表countries表中原来在该地区的国家的region_id。CREATEORREPLACETRIGGERtr_reg_couAFTERupdateOFregion_idONregionsFOREACHROWBEGINDBMS_OUTPUT.PUT_LINE(旧的region_id值是||:old.region_id||、新的region_id值是||:new.region_id);UPDATEcountriesSETregion_id=:new.region_idWHEREregion_id=:old.region_id;END;例5：在触发器中调用过程。CREATEORREPLACEPROCEDUREadd_job_history(p_emp_idjob_history.employee_id%type,p_start_datejob_history.start_date%type,p_end_datejob_history.end_date%type,p_job_idjob_history.job_id%type,p_department_idjob_history.department_id%type)ISBEGININSERTINTOjob_history(employee_id,start_date,end_date,job_id,department_id)VALUES(p_emp_id,p_start_date,p_end_date,p_job_id,p_department_id);ENDadd_job_history;创建触发器调用存储过程...CREATEORREPLACETRIGGERupdate_job_historyAFTERUPDATEOFjob_id,department_idONemployeesFOREACHROWBEGINadd_job_history(:old.employee_id,:old.hire_date,sysdate,:old.job_id,:old.department_id);END;8.2.3创建替代(INSTEADOF)触发器创建触发器的一般语法是:CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOF{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}[OR{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}...]ON[schema.]view_name只能定义在视图上[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW]因为INSTEADOF触发器只能在行级上触发,所以没有必要指定[WHENcondition]PL/SQL_block|CALLprocedure_name;其中：INSTEADOF选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEADOF触发器，而不能对表、模式和数据库建立INSTEADOF触发器。FOREACHROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器;而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOREACHROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEADOF触发器则为行触发器。REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEADOF行触发器和其它类型的触发器中。INSTEAD_OF用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：例1：CREATEORREPLACEVIEWemp_viewASSELECTdeptno,count(*)total_employeer,sum(sal)total_salaryFROMempGROUPBYdeptno;在此视图中直接删除是非法：SQLDELETEFROMemp_viewWHEREdeptno=10;DELETEFROMemp_viewWHEREdeptno=10ERROR位于第1行:ORA-01732:此视图的数据操纵操作非法但是我们可以创建INSTEAD_OF触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：CREATEORREPLACETRIGGERemp_view_deleteINSTEADOFDELETEONemp_viewFOREACHROWBEGINDELETEFROMempWHEREdeptno=:old.deptno;ENDemp_view_delete;DELETEFROMemp_viewWHEREdeptno=10;DROPTRIGGERemp_view_delete;DROPVIEWemp_view;例2：创建复杂视图，针对INSERT操作创建INSTEADOF触发器，向复杂视图插入数据。创建视图：CREATEORREPLACEFORCEVIEWHR.V_REG_COU(R_ID,R_NAME,C_ID,C_NAME)ASSELECTr.region_id,r.region_name,c.country_id,c.country_nameFROMregionsr,countriescWHEREr.region_id=c.region_id;创建触发器：CREATEORREPLACETRIGGERHR.TR_I_O_REG_COUINSTEADOFINSERTONv_reg_couFOREACHROWDECLAREv_countNUMBER;BEGINSELECTCOUNT(*)INTOv_countFROMregionsWHEREregion_id=:new.r_id;IFv_count=0THENINSERTINTOregions(region_id,region_name)VALUES(:new.r_id,:new.r_name);ENDIF;SELECTCOUNT(*)INTOv_countFROMcountriesWHEREcountry_id=:new.c_id;IFv_count=0THENINSERTINTOcountries(country_id,country_name,region_id)VALUES(:new.c_id,:new.c_name,:new.r_id);ENDIF;END;创建INSTEADOF触发器需要注意以下几点：只能被创建在视图上，并且该视图没有指定WITHCHECKOPTION选项。不能指定BEFORE或AFTER选项。FOREACHROW子可是可选的，即INSTEADOF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。没有必要在针对一个表的视图上创建INSTEADOF触发器，只要创建DML触发器就可以了。8.2.3创建系统事件触发器ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE、ALTER及DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：创建触发器的一般语法是:CREATEORREPLACETRIGGER[sachema.]trigger_name{BEFORE|AFTER}{ddl_event_list|database_event_list}ON{DATABASE|[schema.]SCHEMA}[WHENcondition]PL/SQL_block|CALLprocedure_name;其中:ddl_event_list：一个或多个DDL事件，事件间用OR