SQL触发器使用参考代码

《SQL触发器使用参考代码》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、触发器useEBuyifexists(select*fromsys.all_objectswherename='custDel'andtype='TR')--判断custDel存储过程是否存在，存在则删除它droptriggercustDelgocreatetriggercustDeloncustomer--创建存储过程fordelete--触发器类型asprint'客户记录被删除'deletefromcustomerwherecusid='1201'--删除验证select*fromcustomer--查询customer表sp_helptextcustDe

2、l--查看存储过程useStudy--创建新表createtablemajor_stat(majorNamevarchar(50),toStuint,toCreditint)createtriggerupdateMajorStat--创建存储过程onstudentforinsert,update,delete--触发器类型asbegindeletefrommajor_statinsertintomajor_statselectmajorName,count(*),sum(credit)fromstudentsinnerjoinmajormons.majorid

3、=m.majoridgroupbymajorNameendinsertintostudentvalues('071126','李静',2,'男','1985-10-20',42,null)updatestudentsetcredit=credit+2wheremajorid=1deletefromstudentwherestuid='071126'select*fromstudentselect*frommajor_stat--insert触发器useStudygoselect*fromcourseselect*fromstudent_courseselect

4、*fromstudent５ifexists(selectnamefromsys.all_objectswherename='insertScore'andtype='TR')droptriggerinsertScoregocreatetriggerinsertScoreonstudent_courseforinsertasif(selectscorefrominserted)>=60begindeclare@stuidchar(6),@couidchar(3),@creditintselect@stuid=stuid,@couid=couidfrominser

5、tedselect@credit=couCreditfromcoursewherecouid=@couidupdatestudentsetcredit=credit+@creditwherestuid=@stuidendinsertintostudent_coursevalues('071001','C17',70)--update触发器ifexists(selectnamefromsys.all_objectswherename='updateCouHour'andtype='TR')droptriggerinsertScoregocreatetrigger

6、updateCouHouroncourseforupdateasdeclare@oldnumint,@newnumintbeginselect@oldnum=couHourfromdeletedselect@newnum=couHourfrominsertedif@newnum>@oldnumor@newnum<50beginprint'课时不能高于原课时数并且不能低于50'rollbacktranendendgoupdatecoursesetcouHour=couHour-2wherecouidlike'A01'--delete触发器createtrigge

7、rdelMajoronmajorfordeleteasif(selectmajorIdfromdeleted)=1beginprint'不能删除计算专业'rollbacktranendgo５altertablestudentdropconstraintfk_student_majorgoselect*frommajordeletefrommajorwheremajorId=2select*intoquit_stufromstudentwhere1=2--创建一个与student表结构相同的表quit_stucreatetriggerdelStuBackup--

8、创建存储过程(把从student表删除