oracleplsql 编程之八 把触发器说透Word格式.docx
- 文档编号:20780792
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:28
- 大小:24.91KB
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_
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracleplsql 编程之八 把触发器说透 编程 触发器