触发器的一些用法举例.doc

-insert 触发器 create trigger tri_inserton studentfor insert as declare student_id char(10)select student_id=s.student_id from student s inner join inserted ion s.student_id=i.student_idstudent_id=0000000001 begin raiserror(不能插入1学号!,16,8)rollback tran end go =-update触发器 create trigger tri_updateon studentfor update as update(student_id)begin raiserror(学号不能修改!,16,8)rollback tran end go-delete触发器 create trigger tri_deleteon studentfor delete as declare student_id varchar(10)select student_id=student_id from deletedstudent_id=admin begin raiserror(,16,8)rollback tran end禁止删除触发器CREATE TRIGGER NOdeleteon myabcafter deleteasif (select count(*) from deleted)1beginraiserror(你不能删除客户,16,1)rollback transactionend-打印出一条插入的数据select top 10 *from p_customerCREATE TRIGGER PrintValueon myabcdafter insertasdeclare var nvarchar(50)select var=cstname from insertedprint varinsert myabcd (cstguid,cstname)select 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 order datailsafter insertasupdate products set unitinstock=unitinstock-i.quantityfrom order details as odinner join inserted as ductid=ductidinsert order details values (1,1,20,1,0)-值被删除时触发器列出被删除的值CREATE TRIGGER PrintValue2on myabcdafter deleteasselect cstname from deleted-定义Title标题表,Article 文章表-当删除文章区文章时删除相应标题CREATE TRIGGER dArticle on ArticleAFTER DELETEASDELETE FROM Title from title as tleft join article as a on a.titleid=t.titleidwhere a.titleid is null-与添加,删除不同的是SQL server不存在updated表,而是用inserted来存放修改后的数据,用deleted存放修改前的数据CREATE TRIGGER UMyabcON Myabcdafter updateasdeclare var1 varchar(50)declare var2 varchar(50)select var1=cstname from deletedselect var2=cstname from insertedprint 修改前数据email=+var1+var1/emailprint 修改后数据email=+var2+var2/emailupdate top (1) myabcd set cstname=测试触发器-update函数判断究竟该字段是否被修改CREATE TRIGGER UPmyabcon myabcdafter updateasif update(cstname)print 修改elseprint 未改update myabcd set cstname=2 where 1=2select * from myabcd-关闭停用触发器disable trigger UPmyabc on myabcddisable trigger Umyabc on myabcd-启用触发器enable triggerUPmyabc on myabcd-instead of 触发器 (可对表和视图有效,对有instead of触发器的对象执行 delete update insert 命令时并没有改动数据-需要在instead of 触发器中写替代命令-主要用于分区表,分区视图,当视力由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_customerdisable trigger ofmyabc on myabcd-建立数据drop table wangdrop table liuselect * 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 xsinstead of updateasdeclare name varchar(100)set name=(select cstname from inserted)if name=龙飞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 myabcselect * from myabcdisable trigger safety on database-对特定表保护create TRIGGER safety1on databasefor drop_tableasdeclare data xmldeclare tablename nvarchar(max)set data=eventdata()select tablename=eventdata().value(/event_instance/objectname)1,nvarchar(max)if tablename=myabcraiserror(你不能删除这个表,16,1)PRINT 你不能删除这个表rollback tran-测试drop table myabc-建立服务器级别的DDL触发器CREATE TRIGGER ddl