oracleplsql 编程之八 把触发器说透Word格式.docx
《oracleplsql 编程之八 把触发器说透Word格式.docx》由会员分享，可在线阅读，更多相关《oracleplsql 编程之八 把触发器说透Word格式.docx（28页珍藏版）》请在冰豆网上搜索。
DAY"
）IN（"
星期六"
"
星期日"
））OR（TO_CHAR（sysdate,"
HH24:
MI"
）NOTBETWEEN"
08:
30"
AND"
18:
00"
）THEN
RAISE_APPLICATION_ERROR（-20001,"
不是上班时间，不能修改departments表"
）;
ENDIF;
例3：
限定只对部门号为80的记录进行行触发器操作。
CREATEORREPLACETRIGGERtr_emp_sal_comm
BEFOREUPDATEOFsalary,commission_pct
ORDELETE
ONHR.employees
FOREACHROW
WHEN（old.department_id=80）
CASE
WHENUPDATING（"
salary"
IF:
NEW.salary:
old.salaryTHEN
部门80的人员的工资不能降"
commission_pct"
NEW.commission_pct:
mission_pctTHEN
RAISE_APPLICATION_ERROR（-20002,"
部门80的人员的奖金不能降"
WHENDELETINGTHEN
RAISE_APPLICATION_ERROR（-20003,"
不能删除部门80的人员记录"
ENDCASE;
/*
实例：
UPDATEemployeesSETsalary=8000WHEREemployee_id=177;
DELETEFROMemployeesWHEREemployee_idin（177,170）;
*/
例4：
利用行触发器实现级联更新。
在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。
CREATEORREPLACETRIGGERtr_reg_cou
AFTERupdateOFregion_id
ONregions
DBMS_OUTPUT.PUT_LINE（"
旧的region_id值是"
||:
old.region_id
||"
、新的region_id值是"
new.region_id）;
UPDATEcountriesSETregion_id=:
new.region_id
WHEREregion_id=:
old.region_id;
例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
）
IS
INSERTINTOjob_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_history
AFTERUPDATEOFjob_id,department_idONemployees
add_job_history（:
old.employee_id,:
old.hire_date,sysdate,
:
old.job_id,:
old.department_id）;
8.2.3创建替代（INSTEADOF）触发器
创建触发器的一般语法是:
CREATE[ORREPLACE]TRIGGERtrigger_name
INSTEADOF
{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_viewAS
SELECTdeptno,count（*）total_employeer,sum（sal）total_salary
FROMempGROUPBYdeptno;
在此视图中直接删除是非法：
SQL>
DELETEFROMemp_viewWHEREdeptno=10;
DELETEFROMemp_viewWHEREdeptno=10
ERROR位于第1行:
ORA-01732:
此视图的数据操纵操作非法
但是我们可以创建INSTEAD_OF触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：
CREATEORREPLACETRIGGERemp_view_delete
INSTEADOFDELETEONemp_viewFOREACHROW
DELETEFROMempWHEREdeptno=:
old.deptno;
ENDemp_view_delete;
DELETEFROMemp_viewWHEREdeptno=10;
DROPTRIGGERemp_view_delete;
DROPVIEWemp_view;
创建复杂视图，针对INSERT操作创建INSTEADOF触发器，向复杂视图插入数据。
创建视图：
CREATEORREPLACEFORCEVIEW"
HR"
."
V_REG_COU"
（"
R_ID"
R_NAME"
C_ID"
C_NAME"
）
AS
SELECTr.region_id,
r.region_name,
c.country_id,
c.country_name
FROMregionsr,
countriesc
WHEREr.region_id=c.region_id;
创建触发器：
CREATEORREPLACETRIGGER"
TR_I_O_REG_COU"
INSTEADOF
INSERTONv_reg_couFOREACHROWDECLAREv_countNUMBER;
SELECTCOUNT（*）INTOv_countFROMregionsWHEREregion_id=:
new.r_id;
IFv_count=0THEN
INSERTINTOregions
（region_id,region_name
）VALUES
（:
new.r_id,:
new.r_name
）;
SELECTCOUNT（*）INTOv_countFROMcountriesWHEREcountry_id=:
new.c_id;
INSERT
INTOcountries
（
country_id,
country_name,
region_id
VALUES
new.c_id,
new.c_name,
new.r_id
创建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}
其中:
ddl_event_list：
一个或多个DDL事件，事件间用OR分开;
database_event_list：
一个或多个数据库事件，事件间用OR分开;
系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。
当建立在模式（SCHEMA）之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。
当建立在数据库（DATABASE）之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。
要在数据库之上建立触发器时，要求用户具有ADMINISTERDATABASETRIGGER权限。
下面给出系统触发器的种类和事件出现的时机（前或后）：
8.2.4系统触发器事件属性
除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取。
创建触发器，存放有关事件信息。
DESCora_sysevent
DESCora_login_user
创建用于记录事件用的表
CREATETABLEddl_event
（crt_datetimestampPRIMARYKEY,
event_nameVARCHAR2（20）,
user_nameVARCHAR2（10）,
obj_typeVARCHAR2（20）,
obj_nameVARCHAR2（20））;
创建触犯发器
CREATEORREPLACETRIGGERtr_ddl
AFTERDDLONSCHEMA
INSERTINTOddl_eventVALUES
（systimestamp,ora_sysevent,ora_login_user,
ora_dict_obj_type,ora_dict_obj_name）;
ENDtr_ddl;
创建登录、退出触发器。
CREATETABLElog_event
（user_nameVARCHAR2（10）,
addressVARCHAR2（20）,
logon_datetimestamp,
logoff_datetimestamp）;
创建登录触发器
CREATEORREPLACETRIGGERtr_logon
AFTERLOGONONDATABASE
INSERTINTOlog_event（user_name,address,logon_date）
VALUES（ora_login_user,ora_client_ip_address,systimestamp）;
ENDtr_logon;
创建退出触发器
CREATEORREPLACETRIGGERtr_logoff
BEFORELOGOFFONDATABASE
INSERTINTOlog_event（user_name,address,logoff_date）
ENDtr_logoff;
8.2.5使用触发器谓词
谓词
行为
INSERTING
如果触发语句是INSERT语句，则为TRUE,
ORACLE提供三个参数INSERTING,UPDATING,DELETING用于判断触发了哪些操作。
8.2.6重新编译触发器
如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。
当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。
在PL/SQL程序中可以调用ALTERTRIGGER语句重新编译已经创建的触发器，格式为：
ALTERTRIGGER[schema.]trigger_nameCOMPILE[DEBUG]其中：
DEBUG选项要器编译器生成PL/SQL程序条使其所使用的调试代码。
8.3删除和使能触发器
删除触发器：
DROPTRIGGERtrigger_name;
当删除其他用户模式中的触发器名称，需要具有DROPANYTRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTERDATABASETRIGGER系统权限。
此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。
禁用或启用触发器
数据库TRIGGER的状态：
有效状态（ENABLE）：
当触发事件发生时，处于有效状态的数据库触发器TRIGGER将被触发。
无效状态（DISABLE）：
当触发事件发生时，处于无效状态的数据库触发器TRIGGER将不会被触发，此时就跟没有这个数据库触发器（TRIGGER）一样。
数据库TRIGGER的这两种状态可以互相转换。
格式为：
ALTERTIGGERtrigger_name[DISABLE|ENABLE];
例：
ALTERTRIGGERemp_view_deleteDISABLE;
ALTERTRIGGER语句一次只能改变一个触发器的状态，而ALTERTABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。
ALTERTABLE[schema.]table_name{ENABLE|DISABLE}ALLTRIGGERS;
使表EMP上的所有TRIGGER失效：
ALTERTABLEempDISABLEALLTRIGGERS;
8.4触发器和数据字典
相关数据字典：
USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS
SELECTTRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,
TABLE_OWNER,BASE_OBJECT_TYPE,REFERENCING_NAMES,
STATUS,ACTION_TYPE
FROMuser_triggers;
8.5数据库触发器的应用举例
创建一个DML语句级触发器，当对emp表执行INSERT,UPDATE,DELETE操作时，它自动更新dept_summary表中的数据。
由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。
CREATETABLEdept_summary（
DeptnoNUMBER
（2）,
Sal_sumNUMBER（9,2）,
Emp_countNUMBER）;
INSERTINTOdept_summary（deptno,sal_sum,emp_count）
SELECTdeptno,SUM（sal）,COUNT（*）
FROMemp
GROUPBYdeptno;
创建一个PL/SQL过程disp_dept_summary
在触发器中调用该过程显示dept_summary标中的数据。
CREATEORREPLACEPROCEDUREdisp_dept_summary
Recdept_summary%ROWTYPE;
CURSORc1ISSELECT*FROMdept_summary;
OPENc1;
FETCHc1INTOREC;
deptnosal_sumemp_count"
-"
WHILEc1%FOUNDLOOP
DBMS_OUTPUT.PUT_LINE（RPAD（rec.deptno,6）||
To_char（rec.sal_sum,"
$999,999.99"
）||
LPAD（rec.emp_count,13））;
FETCHc1INTOrec;
ENDLOOP;
CLOSEc1;
插入前"
Disp_dept_summary（）;
DBMS_
升级会员 