SQL Server触发器和事务用法示例
本文实例讲述了SQLServer触发器和事务用法。分享给大家供大家参考，具体如下：
新增和删除触发器
altertriggertri_TCont_c forINSERT,delete as begin setXACT_ABORTON declare@INSERTCOUNTint; declare@DELETECOUNTint; declare@UPDATECOUNTint; set@INSERTCOUNT=(selectCOUNT(*)frominserted); set@DELETECOUNT=(selectCOUNT(*)fromdeleted); set@UPDATECOUNT=() if(@INSERTCOUNT>0) begin insertintot_c2select*frominserted; end elseif(@DELETECOUNT>0) begin deletet_c2whereexists(selecttemp.cidfromdeletedtempwheretemp.cid=t_c2.cid); end end
更新触发器和事务
事务主要用在数据的保护，在多表更新时，事务保存所有事务下的更新语句就不会提交，数据也就不能更新成功
altertriggertri_TC_Updateont_c forupdate as begin declare@delcountint; set@delcount=(selectcount(*)fromdeleted); if(@delcount>0) begin begintransactiontriUpdate--定义事务 declare@cnamevarchar(100); select@cname=cnamefrominserted;--保存更新后的内容 updatet_c2setcname=@cnamewherecid=(selectcidfromdeleted);--更新 if(@@error<>0) begin rollbacktransactiontriUpdate;--事务回滚 end else begin committransactiontriUpdate;--事务提交 end end end
存储过程
if(exists(selectnamefromsysobjectsswheres.name='pro_fun'ands.type='p')) dropprocedurepro_fun go createprocedurepro_fun as select*fromtable go execpro_fun
游标
declare@qybhvarchar(10) declarecurcursorfor selectdistinctqybhfromPJ_EnterpriseInput opencur fetchnextfromcurinto@qybh while@@fetch_status=0 begin print(@qybh) fetchnextfromcurinto@qybh end closecur deallocatecur
视图
alterviewCreateView as selectqybhfromCreateView go
定义方法
alterfunctionfunName(@str1varchar(10),@str2varchar(10)) returnsvarchar(10) as begin declare@returnStrvarchar(10) set@returnStr='false' if(@str1>@str2) set@returnStr='true' return@returnStr end selectdbo.funName(...,...)
定义表变量
declare@qybhTabletable(idvarchar(32),qybhvarchar(30)) insertinto@qybhTable selectid,qybhfromPJ_EnterpriseInput select*from@qybhTable
casewhenthen条件统计时的使用
select sum(casewhenz.watchName='注册监理工程师'then1else0end), sum(casewhenz.watchName='xinza'then1else0end), sum(casewhenz.watchName='监理员'then1else0end) fromzu_corjlz rightjoinzu_corjltemptont.corID=z.corID
希望本文所述对大家SQLServer数据库程序设计有所帮助。