oracle触发器.docx
- 文档编号:5053032
- 上传时间:2022-12-12
- 格式:DOCX
- 页数:15
- 大小:21.18KB
oracle触发器.docx
《oracle触发器.docx》由会员分享,可在线阅读,更多相关《oracle触发器.docx(15页珍藏版)》请在冰豆网上搜索。
oracle触发器
Oracle触发器详细介绍
Oracle触发器详细介绍一
触发器
是特定事件出现的时候,自动执行的代码块。
类似于存储过程,但是用户不能直接调用他们。
功能:
1、允许/限制对表的修改
2、自动生成派生列,比如自增字段
3、强制数据一致性
4、提供审计和日志记录
5、防止无效的事务处理
6、启用复杂的业务逻辑
开始
createtriggerbiufer_employees_department_id
beforeinsertorupdate
ofdepartment_id
onemployees
referencingoldasold_value
newasnew_value
foreachrow
when(new_value.department_id<>80)
begin
:
new_mission_pct:
=0;
end;
/
触发器的组成部分:
1、触发器名称
2、触发语句
3、触发器限制
4、触发操作
1、触发器名称
createtriggerbiufer_employees_department_id
命名习惯:
biufer(beforeinsertupdateforeachrow)
employees表名
department_id列名
2、触发语句
比如:
表或视图上的DML语句
DDL语句
数据库关闭或启动,startupshutdown等等
beforeinsertorupdate
ofdepartment_id
onemployees
referencingoldasold_value
newasnew_value
foreachrow
说明:
1、无论是否规定了department_id,对employees表进行insert的时候
2、对employees表的department_id列进行update的时候
3、触发器限制
when(new_value.department_id<>80)
限制不是必须的。
此例表示如果列department_id不等于80的时候,触发器就会执行。
其中的new_value是代表更新之后的值。
4、触发操作
是触发器的主体
begin
:
new_mission_pct:
=0;
end;
主体很简单,就是将更新后的commission_pct列置为0
触发:
insertintoemployees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct)
values(12345,’Chen’,’Donny’,sysdate,12,‘donny@’,60,10000,.25);
selectcommission_pctfromemployeeswhereemployee_id=12345;
触发器不会通知用户,便改变了用户的输入值。
触发器类型:
1、语句触发器
2、行触发器
3、INSTEADOF触发器
4、系统条件触发器
5、用户事件触发器
注释:
before和after:
指在事件发生之前或之后激活触发器。
insteadof:
如果使用此子句,表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update:
指定构成触发器事件的数据操纵类型,update还可以制定列的列表。
referencing:
指定新行(即将更新)和旧行(更新前)的其他名称,默认为new和old。
table_or_view_name:
指要创建触发器的表或视图的名称。
foreachrow:
指定是否对受影响的每行都执行触发器,即行级触发器,如果不使用此子句,则为语句级触发器。
when:
限制执行触发器的条件,该条件可以包括新旧数据值得检查。
declare---end:
是一个标准的PL/SQL块。
Oracle触发器详细介绍二--语句触发器
1、语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。
能够与INSERT、UPDATE、DELETE或者组合上进行关联。
但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。
比如,无论update多少行,也只会调用一次update语句触发器。
例子:
需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。
Createtablefoo(anumber);
Createtriggerbiud_foo
Beforeinsertorupdateordelete
Onfoo
Begin
Ifusernotin(‘DONNY’)then
Raise_application_error(-20001,‘Youdon’thaveaccesstomodifythistable.’);
Endif;
End;
/
即使SYS,SYSTEM用户也不能修改foo表
[试验]
对修改表的时间、人物进行日志记录。
1、建立试验表
createtableemployees_copyasselect*fromhr.employees
2、建立日志表
createtableemployees_log(
whovarchar2(30),
whendate);
3、在employees_copy表上建立语句触发器,在触发器中填充employees_log表。
Createorreplacetriggerbiud_employee_copy
Beforeinsertorupdateordelete
Onemployees_copy
Begin
Insertintoemployees_log(
Who,when)
Values(user,sysdate);
End;
/
4、测试
updateemployees_copysetsalary=salary*1.1;
select*fromemployess_log;
5、确定是哪个语句起作用?
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?
可以在触发器中使用INSERTING/UPDATING/DELETING条件谓词,作判断:
begin
ifinsertingthen
elsifupdatingthen
elsifdeletingthen
endif;
end;
ifupdating(‘COL1’)orupdating(‘COL2’)then
------
endif;
[试验]
1、修改日志表
altertableemployees_log
add(actionvarchar2(20));
2、修改触发器,以便记录语句类型。
Createorreplacetriggerbiud_employee_copy
Beforeinsertorupdateordelete
Onemployees_copy
Declare
L_actionemployees_log.action%type;
Begin
ifinsertingthen
l_action:
=’Insert’;
elsifupdatingthen
l_action:
=’Update’;
elsifdeletingthen
l_action:
=’Delete’;
else
raise_application_error(-20001,’Youshouldneverevergetthiserror.’);
Insertintoemployees_log(
Who,action,when)
Values(user,l_action,sysdate);
End;
/
3、测试
insertintoemployees_copy(employee_id,last_name,email,hire_date,job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select*fromemployees_log
总结:
语句级触发器.(语句级触发器对每个DML语句执行一次)是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。
能够与INSERT、UPDATE、DELETE或者组合上进行关联。
但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。
比如,无论update多少行,也只会调用一次update语句触发器。
实例:
createorreplacetriggertri_test
afterinsertorupdateordeleteontest
begin
ifupdatingthen
dbms_output.put_line('修改');
elsifdeletingthen
dbms_output.put_line('删除');
elsifinsertingthen
dbms_output.put_line('插入');
endif;
end;
Oracle触发器详细介绍三--行级触发器
行级触发器
本章介绍行级触发器机制。
大部分例子以INSERT出发器给出,行级触发器可从insertupdatedelete语句触发。
1、介绍
触发器是存储在数据库已编译的存储过程,使用的语言是PL/SQL,用编写存储过程一样的方式编写和编译触发器。
下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。
这个触发器调用DBMS_OUTPUT在每插入一行数据时打印“executingtemp_air”
SQL>setfeedbackoff
SQL>CREATETABLEtemp(NNUMBER);
SQL>CREATEORREPLACETRIGGERtemp_air
2AFTERINSERTONTEMP
3FOREACHROW
4BEGIN
5dbms_output.put_line('executingtemp_air');
6END;
7/
8SQL>INSERTINTOtempVALUES
(1);--insert1row
executingtemp_air
SQL>INSERTINTOtempSELECT*FROMtemp;--insert1row
executingtemp_air
SQL>INSERTINTOtempSELECT*FROMtemp;--inserts2rows
executingtemp_air
executingtemp_air
SQL>
尽管第三个Insert语句是一条SQL语句,但插入TEMP表中两条记录。
许多insert语句插入一条记录,但可以用一条语句插入许多行。
2、行级触发器语法:
CREATEORREPLACETRIGGERtrigger_name
AFTER|BEFOREINSERT|UPDATE|DELETEONtable_name
FOREACHROW
[WHEN(Booleanexpression)]
DECLARE
Localdeclarations
BEGIN
TriggerBodywrittenPL/SQL
END;
·Trigger_name
用触发器名来确定表名和触发器类型。
PL/SQL运行时错误将产生一个PL/SQL错误信息,涉及触发器名和行数。
下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。
ORA-01476:
divisorisequaltozero
ORA-06512:
at"SCOTT.STUDENTS_AIR",line5
ORA-04088:
errorduringexecutionoftrigger
'SCOTT.STUDENTS_AIR'
行记数从关键字DECLARE行开始,如果没有DECLARE部分,BEGIN语句是第一行。
触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。
触发器名一般由表名、触发器类型、触发事件,语法如下:
trigger_name=table_name_[A|B][I|U|D][R|S]
trigger_name最长30个字符,所以有时不得不使用表名缩写。
常表名一般要有一个规则的缩写。
这样可以减少故障分析处理时间。
[A|B]表示是AFTER或BEFORE触发器类型
[I|U|D]表示触发事件,可能是insert,update或者delete
[R|S]表示行级(row)或语句级(statement)触发器类型。
·BEFORE|AFTERinsertontable_name
这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE完整性约束检查前或后执行,可以指定一个Before或after触发器在多语句操作类型上触发,如:
BEFOREINSERTORUPDATEontable_name
BEFOREINSERTORUPDATEORDELETEontable_name
AFTERINSERTORDELETEontable_name
DBMS_STANDARD包提供了四个boolean函数来区分SQL语句类型。
PACKAGEDBMS_STANDARDIS
FUNCTIONinsertingRETURNBOOLEAN;
FUNCTIONupdatingRETURNBOOLEAN;
FUNCTIONupdating(colnamVARCHAR2)RETURNBOOLEAN;
FUNCTIONdeletingRETURNBOOLEAN;
etc,
ENDDBMS_STANDARD;
在触发器中可以直接使用函数名称,不需要指定包名:
CREATEORREPLACETRIGGERtemp_aiur
AFTERINSERTORUPDATEONTEMP
FOREACHROW
BEGIN
CASE
WHENinsertingTHEN
dbms_output.put_line
('executingtemp_aiur-insert');
WHENupdatingTHEN
dbms_output.put_line
('executingtemp_aiur-update');
ENDCASE;
END;
对于Update行级触发器,可以指定被更新的列作为触发器触发条件。
CREATEORREPLACETRIGGERtemp_aur
AFTERINSERTORUPDATEOFM,PONTEMP
FOREACHROW
BEGIN
dbms_output.put_line
('afterinsertorupdateofm,p');
END;
·WHEN(BOOLEANEXPRESSION)
这是个可选语句,用来过滤触发触发器的条件。
CREATEORREPLACETRIGGERtemp_air
AFTERINSERTONTEMP
FOREACHROW
WHEN(NEW.N=0)
BEGIN
dbms_output.put_line('executingtemp_air');
END;
上例中表示AFTERINSERT行触发器触发的条件是:
N字段的值等于0.
NEW.COLUMN_NAME:
INSERT或UPDATE触发器中WHEN语句中引用字段的语法。
OLD.COLUMN_NAME:
用于UPDATE或DELETE行级触发器中WHEN语句中。
在INSERT语句中为Null。
Oracle触发器详细介绍四--INSTEADOF触发器
在简单视图上往往可以执行INSERT、UPDATE和DELETE操作,但是在复杂视图上执行INSERT、UPDATE和DELETE操作是有限的。
如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询,那么将禁止在该视图上执行DML操作。
为了在这些复杂视图上执行操作,需要建立INSTEAD-OF触发器。
INSTEAD-OF触发器具有以下限制:
INSTEADOF触发器只适用于视图。
INSTEADOF触发器不能指定BEFORE和AFTER选项。
不能在具有WITHCHECKOPTION选项的视图上建立INSTEADOF触发器。
INSTEADOF触发器必须包含有FOREACHROW选项。
复杂视图DEPT_EMP用于显示部门号、部门名、雇员号以及雇员名,并且在该复杂视图上不能执行任何DML操作。
为了在该视图上执行DML操作,必须建立INSTEADOF触发器。
下面以完成该认务为例,说明建立INSTEADOF触发器的方法。
在建立INSTEADOF触发器之前,首先建立视图DEPT_ENP。
createorreplaceviewdept_empas
selecta.deptno,a.dname,b.empno,b.enamefromdepta,empb
wherea.deptno=b.deptno;
createorreplacetriggertr_instead_of_dept_emp
insteadofinsertondept_empforeachrow
declare
v_tempint;
begin
selectcount(*)intov_tempfromdeptwheredeptno=:
new.deptno;
ifv_temp=0then
insertintodept(deptno,dname)values(:
new.deptno,:
new.dname);
endif;
selectcount(*)intov_tempfromempwhereempno=:
new.empno;
ifv_temp=0then
insertintoemp(empno,ename,deptno)values(:
new.empno,:
new.ename,:
new.deptno);
endif;
end;
/
createorreplacetriggertr_update_of_dept_emp
insteadofupdateondept_empforeachrow
declare
v_tempint;
begin
selectcount(*)intov_tempfromdeptwheredeptno=:
new.deptno;
ifv_temp=0then
updatedeptsetdeptno=:
new.deptno,dname=:
new.dname;
endif;
selectcount(*)intov_tempfromempwhereempno=:
new.empno;
ifv_temp=0then
updateempsetempno=:
new.empno,ename=:
new.ename,deptno=:
new.deptno;
endif;
end;
/
Oracle触发器详细介绍五--系统事件触发器
oracle的系统事件触发器:
系统事件触发器是指基于oracle系统事件(如logon和startup)所建立的触发器。
通过这种触发器可以跟踪系统或数据库的变化。
createtablejax_event_table(eventnamevarchar2(30),timedate);
createtriggertr_startup
afterstartupondatabase
begin
insertintojax_event_tablevalues(ora_sysevent,sysdate);
end;
createtriggertr_shutdown
beforeshutdownondatabase
begin
insertintojax_event_tablevalues(ora_sysevent,sysdate);
end;
在建立如上所示的两个触发器后,使用shutdown和startup关闭开启数据库会往表jax_event_table中记录一条记录,但shutdownabort则不会触发该触发器,而startupnomount后使用alterdatabase将数据库更改为mount或者open都只会触发一次。
1SHUTDOWN2008-3-2014:
29:
47
2STARTUP2008-3-2014:
42:
52
3SHUTDOWN2008-3-2014:
43:
06
4STARTUP2008-3-2014:
45:
34
登录和退出触发器用来记载登录用户名称、时间和ip地址
createtablejax_log_table(
usernamevarchar2(20),log_timedate,onoffvarchar(6),addressvarchar2(30));
createtriggertr_logon
afterlogonondatabase
b
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 触发器
![提示](https://static.bdocx.com/images/bang_tan.gif)