SQL Server触发器和事务用法示例

前端之家收集整理的这篇文章主要介绍了SQL Server触发器和事务用法示例前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

本文实例讲述了sql Server触发器和事务用法分享给大家供大家参考,具体如下:

新增和删除触发器

0) begin insert into t_c2 select * from inserted; end else if(@DELETECOUNT > 0) begin delete t_c2 where exists(select temp.cid from deleted temp where temp.cid=t_c2.cid); end end

更新触发器和事务

事务主要用在数据的保护,在多表更新时,事务保存所有事务下的更新语句就不会提交,数据也就不能更新成功

0) begin begin transaction triUpdate --定义事务 declare @cname varchar(100); select @cname = cname from inserted; --保存更新后的内容 update t_c2 set cname = @cname where cid = (select cid from deleted); --更新 if (@@error <> 0) begin rollback transaction triUpdate; --事务回滚 end else begin commit transaction triUpdate; --事务提交 end end end

存储过程

sql;"> if(exists(select name from sysobjects s where s.name='pro_fun' and s.type='p')) drop procedure pro_fun go create procedure pro_fun as select * from table go exec pro_fun

游标

sql;"> declare @qybh varchar(10) declare cur cursor for select distinct qybh from PJ_EnterpriseInput open cur fetch next from cur into @qybh while @@fetch_status = 0 begin print(@qybh) fetch next from cur into @qybh end close cur deallocate cur

视图

sql;"> alter view CreateView as select qybh from CreateView go

定义方法

@str2) set @returnStr = 'true' return @returnStr end select dbo.funName(...,...)

定义表变量

sql;"> declare @qybhTable table (id varchar(32),qybh varchar(30)) insert into @qybhTable select id,qybh from PJ_EnterpriseInput select * from @qybhTable

case when then 条件统计时的使用

sql;"> select sum(case when z.watchName='注册监理工程师' then 1 else 0 end),sum(case when z.watchName='xinza' then 1 else 0 end),sum(case when z.watchName='监理员' then 1 else 0 end) from zu_corjl z right join zu_corjltemp t on t.corID=z.corID

希望本文所述对大家sql Server数据库程序设计有所帮助。

猜你在找的MsSQL相关文章