触发器语句.docx
《触发器语句.docx》由会员分享，可在线阅读，更多相关《触发器语句.docx（117页珍藏版）》请在冰点文库上搜索。
触发器语句
您还未登录！
|登录|注册|帮助
CSDN首页
资讯
论坛
博客
下载
搜索
更多
CTO俱乐部
学生大本营
培训充电
移动开发
软件研发
云计算
程序员
ITeye
TUP
chinayuan的专栏
目录视图
摘要视图
订阅
精创之作《雷神的微软平台安全宝典》诚邀译者 移动业界领袖会议·上海·6.20
CSDN博客频道“移动开发之我见”主题征文活动 【分享季1】：
网友推荐130个经典资源，分享再赠分！
数据库触发器
分类：
数据库2011-03-3118:
002889人阅读评论
（2）收藏举报
触发器
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、用户事件触发器
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_logadd（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
updateemployees_copysetsalary=50000whereemployee_id=12345;
2、行触发器
是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：
1、定义语句中包含FOREACHROW子句
2、在BEFORE……FOREACHROW触发器中，用户可以引用受到影响的行值。
比如：
定义：
createtriggerbiufer_employees_department_id
beforeinsertorupdate
ofdepartment_id
onemployees_copy
referencingoldasold_value
newasnew_value
foreachrow
when（new_value.department_id<>80）
begin
:
new_mission_pct:
=0;
end;
/
Referencing子句：
执行DML语句之前的值的默认名称是:
old,之后的值是:
new
insert操作只有:
new
delete操作只有:
old
update操作两者都有
referencing子句只是将new和old重命名为new_value和old_value，目的是避免混淆。
比如操作一个名为new的表时。
作用不很大。
[试验]：
为主健生成自增序列号
droptablefoo;
createtablefoo（idnumber,datavarchar2（20））;
createsequencefoo_seq;
createorreplacetriggerbifer_foo_id_pk
beforeinsertonfoo
foreachrow
begin
selectfoo_seq.nextvalinto:
new.idfromdual;
end;
/
insertintofoo（data）values（‘donny’）;
insertintofoovalues（5,’Chen’）;
select*fromfoo;
3、INSTEADOF触发器更新视图
insteadof触发器，可以实现:
不执行导致trigger触发的语句，而只执行触发器.
INSTEADOFtriggersprovideatransparentwayofmodifyingviewsthatcannotbemodifieddirectlythroughDMLstatements（INSERT,UPDATE,andDELETE）.ThesetriggersarecalledINSTEADOFtriggersbecause,unlikeothertypesoftriggers,Oraclefiresthetriggerinsteadofexecutingthetriggeringstatement.YoucanwritenormalINSERT,UPDATE,andDELETEstatementsagainsttheviewandtheINSTEADOFtriggerisfiredtoupdatetheunderlyingtablesappropriately.INSTEADOFtriggersareactivatedforeachrowoftheviewthatgetsmodified.
Createorreplaceviewcompany_phone_bookas
Selectfirst_name||’,’||last_namename,email,phone_number,employee_idemp_id
Fromhr.employees;
尝试更新email和name
updatepany_phone_booksetname=’Chen1,Donny1’whereemp_id=100
createorreplacetriggerupdate_name_company_phone_book
INSTEADOF
Updateonpany_phone_book
Begin
Updatehr.employees
Setemployee_id=:
new.emp_id,
First_name=substr（:
new.name,instr（:
new.name,’,’）+2）,
last_name=substr（:
new.name,1,instr（:
new.name,’,’）-1）,
phone_number=:
new.phone_number,
email=:
new.email
whereemployee_id=:
old.emp_id;
end;
insteadoftrigger是基于视图建立的,不能建在表上,为什么要建在视图上,一般的视图如果其数据来源一个表并且包含该表的主键,就可以对视图进行DML操作.另外一种情况是从多个表查询出来的.这样我们就不能对视图进行操作了,也就是只能查询.insteadoftrigger可以解决建在多表上视图的更新操作.
下面我们就来实例操作:
a.先建表,简单点就三个分别是学生表,课程表,学生选课表
CREATETABLESTUDENT
（
CODE VARCHAR2（5）,
LNAMEVARCHAR2（200）
）
CREATETABLECOURSE
（
CODE VARCHAR2（5）,
CNAMEVARCHAR2（30）
）
CREATETABLEST_CR
（
STUDENTVARCHAR2（5）,
COURSE VARCHAR2（5）,
GRADE NUMBER
）
--表的约束
ALTERTABLESTUDENTADDCONSTRAINTSTUDENT$PKPRIMARYKEY（CODE）;
ALTERTABLECOURSEADDCONSTRAINTCOURSE$PKPRIMARYKEY（CODE）;
ALTERTABLEST_CRADDCONSTRAINTST_CR$PKPRIMARYKEY（STUDENT,COURSE）;
ALTERTABLEST_CRADDCONSTRAINTST_CR$FK$STUDENTFOREIGNKEY（STUDENT）REFERENCESSTUDENT（CODE）;
ALTERTABLEST_CRADDCONSTRAINTST_CR$FK$COURSEFOREIGNKEY（COURSE）REFERENCESCOURSE（CODE）;
b.基于这三个表的视图
CREATEORREPLACEVIEWSTUDENT_STATUSAS
SELECTS.CODES_CODE,S.LNAMESTUDENT,C.CODEC_CODE,C.CNAMECOURSE,SC.GRADEGRADE
FROMSTUDENTS,COURSEC,ST_CRSC
WHERES.CODE=SC.STUDENT
ANDC.CODE=SC.COURSE
c.基于视图的触发器
CREATEORREPLACETRIGGERTRI_STCRINSTEADOFINSERTONSTUDENT_STATUS
FOREACHROW
DECLARE
W_ACTIONVARCHAR2
（1）;
BEGIN
IF INSERTINGTHEN
W_ACTION:
='I';
ELSE
RAISEPROGRAM_ERROR;
ENDIF;
INSERTINTOSTUDENT（CODE,LNAME）VALUES（:
NEW.S_CODE,:
NEW.STUDENT）;
INSERTINTOCOURSE（CODE,CNAME）VALUES（:
NEW.C_CODE,:
NEW.COURSE）;
INSERTINTOST_CR（STUDENT,COURSE,GRADE）
VALUES（:
NEW.S_CODE,:
NEW.C_CODE,:
NEW.GRADE）;
END;
d.对视图执行数据插入
INSERTINTOSTUDENT_STATUS（S_CODE,STUDENT,C_CODE,COURSE,GRADE）
VALUES（'001','Mike','EN','English',86）;
可以看到每个表各有一条数据已经插入.
4、系统事件触发器
系统事件：
数据库启动、关闭，服务器错误
createtriggerad_startup
afterstartup
ondatabase
begin
--dosomestuff
end;
/
5、用户事件触发器
用户事件：
用户登陆、注销，CREATE/ALTER/DROP/ANALYZE/AUDIT/GRANT/REVOKE/RENAME/TRUNCATE/LOGOFF
例子：
记录删除对象
1.日志表
createtabledroped_objects（
object_namevarchar2（30）,
object_typevarchar2（30）,
dropped_ondate）;
2．触发器
createorreplacetriggerlog_drop_trigger
beforedropondonny.schema
begin
insertintodroped_objectsvalues（
ora_dict_obj_name, --与触发器相关的函数
ora_dict_obj_type,
sysdate）;
end;
/
3.测试
createtabledrop_me（anumber）;
createviewdrop_me_viewasselect*fromdrop_me;
dropviewdrop_me_view;
droptabledrop_me;
select*fromdroped_objects
禁用和启用触发器
altertriggerdisable;
altertriggerenable;
事务处理：
在触发器中，不能使用commit/rollback,因为ddl语句具有隐式的commit，所以也不允许使用
视图：
dba_triggers
MSSQLServer触发器:
SQLSever2005包含的3个触发器对象:
AFTER，数据定义语言（DDL）和INSTEAD-OF
1． AFTER触发器是存储程序，它发生于数据操作语句作用之后，例如删除语句等。
2． DDL是SQLServer2005的新触发器，允许响应数据库引擎中对象定义水平事件（例如:
DROPTABLE语句）。
3． INSTEAD-OF触发器是对象，在数据库引擎中可以取代数据操作语句而执行。
例如:
将INSTEAD-OFINSERT触发器附加到表，告诉数据库执行此触发器
SQLServer2005中DDL触发器的实现
SQLSERVER2005中，新增加了许多新的特性，其中的DDL触发器是个不错的选择，根据资料初步学习如下，现整理之：
在sqlserver2000中，只能为针对表发出的DML语句（INSERT、UPDATE和DELETE）定义AFTER触发器。
SQLServer2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。
可以为单个DDL语句（例如，CREATE_TABLE）或者为一组语句（例如，DDL_DATABASE_LEVEL_EVENTS）定义DDL触发器。
在该触发器内部，您可以通过访问eventdata（）函数获得与激发该触发器的事件有关的数据。
该函数返回有关事件的XML数据。
每个事件的架构都继承了ServerEvents基础架构。
比如，在SQLSERVER2005中，建立一个叫DDLTrTest的数据库，并且建立一个叫
升级会员 