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

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

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

新增和删除触发器

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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

更新触发器和事务

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

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

存储过程

?

1

2

3

4

5

6

7

8

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

游标

?

1

2

3

4

5

6

7

8

9

10

11

12

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

视图

?

1

2

3

4

alter view CreateView

as

select qybh from CreateView

go

定义方法

?

1

2

3

4

5

6

7

8

9

10

11

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(… , …)

定义表变量

?

1

2

3

4

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

insert into @qybhTable

select id,qybh from PJ_EnterpriseInput

select * from @qybhTable

case when then 条件统计时的使用

?

1

2

3

4

5

6

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数据库程序设计有所帮助。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26309/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-27 8:24:45
下一篇 2022-09-27 8:24:51

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!