《触发器的一些用法举例》由会员分享，可在线阅读，更多相关《触发器的一些用法举例（5页珍藏版）》请在金锄头文库上搜索。
1、-insert 触发器create trigger tri_inserton studentfor insertasdeclare student_id char(10)select student_id=s.student_id from student s inner join inserted i on s.student_id=i.student_idif student_id=OOOOOOOOO1beginraiserror(不能插入 1 学号!,16,8) rollback tran endgo -update 触发器create trigger tri_update on stu
2、dent for updateasupdate(student_id)beginraiserror(学号不能修改!,16,8)rollback tran end go-delete触发器create trigger tri_deleteon studentfor deleteasdeclare student_id varchar(10)select student_id=student_id from deleted student_id=adminbeginraiserror(错天,16,8)rollback tranend禁止删除触发器CREATE TRIGGER NOdelete on
3、 myabc after delete asif (select count(*) from deleted)1 beginraiserror( 你不能删除客户 ,16,1) rollback transaction end-打印出一条插入的数据select top 10 * from p_customerCREATE TRIGGER PrintValueon myabcdafter insertasdeclare var nvarchar(50)select var=cstname from inserted print varinsert myabcd (cstguid,cstname)s
4、elect 985575ED-CC05-4B93-97A6-0007D741D170,trigger-打印多条CREATE TRIGGER PrintValue1on myabcdafter insertasselect cstname from insertedinsert myabcd (cstguid,cstname)select 985575ED-CC05-4B93-97A6-0007D741D170,trigger-撰写触发器，使用join来处理库存的更新-创建表略-当订单多一条数据时库存表就相应减少,这由触发器来完成CREATE TRIGGER orderdetails on or
5、der datails after insertasupdate products set unitinstock=unitinstock-i.quantity from order details as od inner join inserted as ion od.productid=i.productidinsert order details values (1,1,20,1,0)-值被删除时触发器列出被删除的值CREATE TRIGGER PrintValue2on myabcdafter deleteasselect cstname from deleted-定义Title标题表
6、,Article文章表-当删除文章区文章时删除相应标题CREATE TRIGGER dArticle on ArticleAFTER DELETEASDELETE FROM Title from title as tleft join article as a on a.titleid=t.titleid where a.titleid is null-与添加，删除不同的是SQL server不存在updated表,而是用 inserted来存放修改后的数据,用deleted存放修改前的数据CREATE TRIGGER UMyabcON Myabcdafter updateasdeclare
7、var1 varchar(50)declare var2 varchar(50)select var1=cstname from deletedselect var2=cstname from insertedprint 修改前数据email二+varl+varl/emailprint 修改后数据email二+var2+var2/emailupdate top (1) myabcd set cstname二测试触发器-update 函数判断究竟该字段是否被修改CREATE TRIGGER UPmyabcon myabcdafter updateasif update(cstname) prin
8、t 修改 else print 未改update myabcd set cstname=2 where 1=2 select * from myabcd-关闭停用触发器disable trigger UPmyabc on myabcd disable trigger Umyabc on myabcd-启用触发器enable trigger UPmyabc on myabcdinstead of触发器(可对表和视图有效，对有instead of触发器的对象执行 delete update insert 命令时并没有改动数据-需要在 instead of 触发器中写替代命令-主要用于分区表，分区视
9、图，当视力由UNION ALL连接而成时无法直接更新视 图，就需要instead of触发器来完成 create trigger ofmyabc on myabcdinstead of delete，insert，updateasprint 没有更新数据-测试delete myabcdselect * from myabcdupdate myabcd set cstname=8insert myabcdselect top 1 * from p_customer disable trigger ofmyabc on myabcd-建立数据drop table wang drop table l
10、iu select * into wang from p_customer where cstname= 龙飞select * into liu from p_customer where cstname二李寻欢 create view xsasselect * from wangunion allselect * from liu-建立 instead of 触发器 alter trigger inx on xs instead of updateasdeclare name varchar(100)set name=(select cstname from inserted)if name
11、= 龙飞 print 更新龙飞 else if name= 李寻欢 print 更新李寻欢-测试update xs set gender= 女 where cstname= 龙飞 update xs set gender= 女男 where cstname= 李寻欢-DDL 触发器响应表级别或服务器级别的事件 CREATE TRIGGER safetyon databasefor drop_tableasraiserror( 你不能删除表 ,16,1)rollback transactiongo-测试drop table myabc select * from myabc disable tr
12、igger safety on database-对特定表保护create TRIGGER safety1on databasefor drop_tableasdeclare data xmldeclare tablename nvarchar(max) set data=eventdata()selecttablename=eventdata().value(/event_instance/objectname)1,nvarc har(max)if tablename=myabcraiserror( 你不能删除这个表 ,16,1)PRINT 你不能删除这个表 rollback tran-测试drop table myabc-建立服务器级别的DDL触发器CREATE TRIGGER ddl_trig_loginon all serverfor ddl_login_eventsasprint 登录事件selecteventdata().value(/event_instance/tsqlcommand/commandtext)1,nva rchar(max)sp_addlogin wangyong,1234123456789drop login wangyong
