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

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

2020-05-08 15:08ajunfly Sql Server

这篇文章主要介绍了SQL Server触发器和事务用法,结合实例形式分析了SQL Server触发器、事务、存储过程、游标、视图等的相关定义与使用方法,需要的朋友可以参考下

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

新增和删除触发器

									alter trigger tri_TC on t_c

									  for INSERT,delete

									as

									begin

									  set XACT_ABORT ON

									  declare @INSERTCOUNT int;

									  declare @DELETECOUNT int;

									  declare @UPDATECOUNT int;

									  set @INSERTCOUNT = (select COUNT(*) from inserted);

									  set @DELETECOUNT = (select COUNT(*) from deleted);

									  set @UPDATECOUNT = ()

									  if(@INSERTCOUNT > 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

更新触发器和事务

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

									alter trigger tri_TC_Update on t_c

									  for update

									as

									begin

									  declare @delcount int;

									  set @delcount = (select count(*) from deleted);

									  if(@delcount > 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

存储过程

									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

游标

									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

视图

									alter view CreateView

									as

									 select qybh from CreateView

									go

定义方法

									alter function funName(@str1 varchar(10),@str2 varchar(10))

									returns varchar(10)

									as

									begin

									  declare @returnStr varchar(10)

									  set @returnStr = 'false'

									  if(@str1 > @str2)

									    set @returnStr = 'true'

									  return @returnStr

									end

									select dbo.funName(... , ...)

定义表变量

									declare @qybhTable table (id varchar(32),qybh varchar(30))

									insert into @qybhTable

									select id,qybh from PJ_EnterpriseInput

									select * from @qybhTable

case when then 条件统计时的使用

									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