触发器的一些用举例.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_id@student_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 deleted@student_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 datails]after insertasupdate products set unitinstock=unitinstock-i.quantityfrom [order details] as odinner 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标题表,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 fr