PLSQL触发器分类.docx
《PLSQL触发器分类.docx》由会员分享,可在线阅读,更多相关《PLSQL触发器分类.docx(15页珍藏版)》请在冰豆网上搜索。
![PLSQL触发器分类.docx](https://file1.bdocx.com/fileroot1/2023-2/9/a23a6703-5991-4f0e-ad28-353db5d8d22f/a23a6703-5991-4f0e-ad28-353db5d8d22f1.gif)
PLSQL触发器分类
函数(function)可以在查询语句中直接使用
存储过程(procedure)无返回值,通过输出参数返回。
必须单独调用
Inout会用到中间变量,中间值不断变化会输入和输出
触发器在数据库里以独立的对象存储,它与存储过程不同的是,存储过程通过其它程序来启动运行或直接启动运行,而触发器是由一个事件来启动运行。
即触发器是当某个事件发生时自动地隐式运行。
并且,触发器不能接收参数。
所以运行触发器就叫触发或点火(firing)。
ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。
ORACLE将触发器的功能扩展到了触发ORACLE,如数据库的启动与关闭等。
主要的触发器种类有三种:
ØDML触发器:
ORACLE可以在DML语句进行触发,可以在DML操作前或操作后进行触发,并且可以对每个行或语句操作上进行触发
Ø替代触发器:
由于在ORACLE里,不能直接对由两个以上的表建立的视图进行操作。
所以给出了替代触发器。
它就是ORACLE专门为进行视图操作的一种处理方法。
Ø系统触发器
它可以在ORACLE数据库系统的事件中进行触发,如ORACLE系统的启动与关闭等。
触发器的组成主要有:
Ø触发事件:
即在何种情况下触发TRIGGER;例如:
INSERT,UPDATE,DELETE。
Ø触发时间:
即该TRIGGER是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该TRIGGER的操作顺序。
Ø触发频率:
说明触发器内定义的动作被执行的次数。
即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器:
是指当某触发事件发生时,该触发器只执行一次;
行级(ROW)触发器:
是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。
Ø触发器本身:
即该TRIGGER被触发之后的目的和意图,正是触发器本身要做的事情。
例如:
PL/SQL块。
创建触发器
创建触发器的一般语法是:
CREATE[ORREPLACE]TRIGGERtrigger_name
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}
ON[schema.]table_name
[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}]
[FOREACHROW]
[WHENcondition]
trigger_body;
其中:
BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。
FOREACHROW选项说明触发器为行触发器。
行触发器和语句触发器的区别表现在:
行触发器要求当一个DML语句操走影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。
当省略FOREACHROW选项时,BEFORE和AFTER触发器为语句触发器,而INSTEADOF触发器则为行触发器。
REFERENCING子句说明相关名称,在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值,默认的相关名称分别为OLD和NEW。
触发器的PL/SQL块中应用相关名称时,必须在它们之前加冒号(:
),但在WHEN子句中则不能加冒号。
其中:
WHEN子句说明触发约束条件。
Condition为一个逻辑表达时,其中必须包含相关名称,而不能包含查询语句,也不能调用PL/SQL函数。
WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中,不能用在INSTEADOF行触发器和其它类型的触发器中。
当一个基表被修改(INSERT,UPDATE,DELETE)时要执行的存储过程,执行时根据其所依附的基表改动而自动触发,因此与应用程序无关,用数据库触发器可以保证数据的一致性和完整性。
每张表最多可创建12种触发器:
BEFOREINSERT
BEFOREINSERTFOREACHROW
AFTERINSERT
AFTERINSERTFOREACHROW
BEFOREUPDATE
BEFOREUPDATEFOREACHROW
AFTERUPDATE
AFTERUPDATEFOREACHROW
BEFOREDELETE
BEFOREDELETEFOREACHROW
AFTERDELETE
AFTERDELETEFOREACHROW
触发器执行顺序为:
Ø执行BEFORE语句级触发器
Ø对与受语句影响的每一行:
✓执行BEFORE行级触发器
✓执行DML语句
✓执行AFTER行级触发器
Ø执行AFTER语句级触发器
创建DML触发器
触发器名与过程名和包的名字不一样,它是单独的名字空间,因而触发器名可以和表或过程有相同的名字,但在一个模式中触发器名不能相同
例:
CREATEORREPLACETRIGGERdel_emp
BEFOREDELETEONscott.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,:
m,:
old.hiredate);
END;
触发器的限制
ØCREATETRIGGER语句文本的字符长度不能超过32KB
Ø触发器体内的SELECT语句只能为SELECT…INTO…结构,或者为定义游标所使用的SELECT语句
Ø触发器中不能使用数据库事务控制语句COMMIT,ROLLBACK,SVAEPOINT语句
Ø由触发器所调用的过程或函数也不能使用数据库事务控制语句
Ø触发器中不能使用LONG,LONGRAW类型
Ø触发器内可以参照LOB类型列的列值,但不能通过:
NEW修改LOB列中的数据
行级别触发器中的相关标识符
当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值,可以使用:
:
NEW修饰符访问操作完成后列的值
:
OLD修饰符访问操作完成前列的值
特性
INSERT
UPDATE
DELETE
OLD
NULL
有效
有效
NEW
有效
有效
NULL
创建DML触发器
CREATEORREPLACETRIGGERdel_emp
BEFOREupdateONscott.emp
REFERENCINGnewASnnoldASoo
FOREACHROW
WHEN(nn.sal>2000)
BEGIN
dbms_output.put_line(:
nn.sal||'------'||:
oo.sal);
END;
DML触发器中的谓词
在DML触发器中,他们被不同的DML语句所触发,有三个布尔型函数来确定操作到底是什么:
谓词
描述
INSERTING
如果触发语句是INSERT,则为TRUE,否则为FALSE
UPDATING
如果触发语句是UPDATE,则为TRUE,否则为FALSE
DELETING
如果触发语句是DELETE,则为TRUE,否则为FALSE
CREATEORREPLACETRIGGERcheck_emp
BEFOREupdateORinsertORdeleteONscott.emp
REFERENCINGnewASnnoldASoo
FOREACHROW
WHEN(nn.sal>2000)
BEGIN
IFINSERTINGTHEN
dbms_output.put_line('THEOPERATIONISINSERT');
ELSIFUPDATINGTHEN
dbms_output.put_line('THEOPERATIONISUPDATE');
ELSIFDELETINGTHEN
dbms_output.put_line('THEOPERATIONISDELETE');
ELSE
dbms_output.put_line('OTHERSOPERATION');
ENDIF;
END;
创建替代触发器
创建触发器的一般语法是:
CREATE[ORREPLACE]TRIGGERtrigger_name
INSTEADOF
{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}
ON[schema.]view_name
[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}]
[FOREACHROW]
[WHENcondition]
trigger_body;
其中:
BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。
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)而成,因而并非是所有的联结都是可更新的。
但可以按照所需的方式执行更新
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
BEGIN
DELETEFROMempWHEREdeptno=:
old.deptno;
ENDemp_view_delete;
DELETEFROMemp_viewWHEREdeptno=10;
创建系统触发器
系统触发器可以在DDL或数据库系统上被触发。
DDL指的是数据定义语言,如CREATE、ALTER及DROP等。
而数据库系统事件包括数据库服务器的启动或关闭,用户的登录与退出、数据库服务错误等。
创建系统触发器的语法如下
创建系统触发器的一般语法是:
CREATEORREPLACETRIGGER[sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list|database_event_list}
ON{DATABASE|[schema.]SCHEMA}
[WHEN_clause]
trigger_body;
其中:
ddl_event_list为一个或多个DDL事件,事件间用OR分开;
database_event_list为一个或多个数据库事件,事件间用OR分开;
注意:
系统事件触发器既可以建立在一个模式上,又可以建立在整个数据库上。
当建立在模式(SCHEMA)之上时,只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器,默认时为当前用户模式。
当建立在数据库(DATABASE)之上时,该数据库所有用户的DDL操作和他们所导致的错误,以及数据库的启动和关闭均可激活触发器。
要在数据库之上建立触发器时,要求用户具有ADMINISTERDATABASETRIGGER权限。
系统触发器的种类和事件出现的时机(前或后):
事件
允许的时机
说明
启动STARTUP
之后
实例启动时激活
关闭SHUTDOWN
之前
实例正常关闭时激活
服务器错误SERVERERROR
之后
只要有错误就激活
登录LOGON
之后
成功登录后激活
注销LOGOFF
之前
开始注销时激活
创建CREATE
之前,之后
在创建之前或之后激活
撤消DROP
之前,之后
在撤消之前或之后激活
变更ALTER
之前,之后
在变更之前或之后激活
创建系统触发器应用举例
CREATEORREPLACETRIGGERtrig4_ddl
AFTERCREATEORALTERORDROPONDATABASE
DECLARE
EventVARCHAR2(20);TypVARCHAR2(20);
NameVARCHAR2(30);OwnerVARCHAR2(30);
BEGIN
--读取DDL事件属性
Event:
=SYSEVENT;
Typ:
=DICTIONARY_OBJ_TYPE;
Name:
=DICTIONARY_OBJ_NAME;
Owner:
=DICTIONARY_OBJ_OWNER;
--将事件属性插入到事件日志表中
INSERTINTOscott.eventlog(eventname,obj_type,obj_name,obj_owner)
VALUES(event,typ,name,owner);
END;
使用触发器谓词
ORACLE提供三个参数INSERTING,UPDATING,DELETING用于判断触发了哪些操作。
谓词
行为
INSERTING
如果触发语句是INSERT语句,则为TRUE,否则为FALSE
UPDATING
如果触发语句是UPDATE语句,则为TRUE,否则为FALSE
DELETING
如果触发语句是DELETE语句,则为TRUE,否则为FALSE
重新编译触发器
如果在触发器内调用其它函数或过程,当这些函数或过程被删除或修改后,触发器的状态将被标识为无效。
当DML语句激活一个无效触发器时,ORACLE将重新编译触发器代码,如果编译时发现错误,这将导致DML语句执行失败。
删除触发器
删除触发器使用下面的语句:
DROPTRIGGERtrigger_name;
当删除其他用户模式中的触发器名称,需要具有DROPANYTRIGGER系统权限,当删除建立在数据库上的触发器时,用户需要具有ADMINISTERDATABASETRIGGER系统权限。
此外,当删除表或视图时,建立在这些对象上的触发器也随之删除
触发器状态
Ø有效状态(ENABLE):
当触发事件发生时,处于有效状态的数据库触发器TRIGGER将被触发。
Ø无效状态(DISABLE):
当触发事件发生时,处于无效状态的数据库触发器TRIGGER将不会被触发,此时就跟没有这个数据库触发器(TRIGGER)一样。
数据库TRIGGER的这两种状态可以互相转换。
格式为:
ALTERTIGGERtrigger_name[DISABLE|ENABLE];
例:
ALTERTRIGGERemp_view_deleteDISABLE;
ALTERTRIGGER语句一次只能改变一个触发器的状态,而ALTERTABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。
格式为:
ALTERTABLE[schema.]table_name{ENABLE|DISABLE}ALL
使表EMP上的所有TRIGGER失效:
ALTERTABLEempDISABLEALLTRIGGERS;
触发器和数据字典
相关数据字典:
USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS
SELECTTRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,
TABLE_OWNER,BASE_OBJECT_TYPE,REFERENCING_NAMES,
STATUS,ACTION_TYPE
FROMuser_triggers;
触发器
自动备份删除的数据
备份表
createtabledept_bak
as
select*fromdeptwhere2=3;
select*fromdept_bak
创建触发器
createorreplacetriggerdel_dept_tri
after
delete
ondept
foreachrow
declare
begin
insertintodept_bakvalues(:
old.deptno,:
old.dname,:
old.loc);
end;
deletefromdept_bak;
删除触发器
droptriggerdept_dak_tri
在插入数据的时候自动生产唯一的标识
createsequencedept_seqstartwith70;
创建触发器
CREATEORREPLACETRIGGERinsert_dept_tri
BEFORE
INSERT
ONdept
FOREACHROW
DECLARE
BEGIN
SELECTdept_seq.nextvalINTO:
new.detnoFROMdual;
END;
替代视图
CREATEORREPLACEVIEWv_dept
AS
SELECTe.department_id,avg(e.salary)ASsal_avg
FROMemployeese
GROUPBYe.department_id;
CREATEORREPLACETRIGGERv_dept_tri
INSTEADOF
DELETE
ONv_dept
FOREACHROW
DECLARE
BEGIN
DELETEFROMemployeesWHEREdepartment_id=:
old.department_id;
END;