SQL Server手写数据库事务、存储过程脚本

枫杨NET

已于 2022-05-29 09:33:19 修改

阅读量282

点赞数

分类专栏： SQL 文章标签： sql

于 2019-12-24 17:32:31 首次发布

本文链接：https://blog.csdn.net/qq_36695087/article/details/103686168

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL Server手写数据库事务、存储过程脚本
1.事务

--卡号为115的账户给卡号为116的账户转账100元
declare @errorSum int = 0
Begin Transaction
Begin
	Update User set BalanceCount = BalanceCount - 100 where UserId = 115
	@errorSum += @@Error
	Update User set BalanceCount = BalanceCount + 100 where UserId = 116
	@errorSum += @@Error
	if(@errorSum > 0)
		RollBack Transaction
	else
		Commit Transaction
End

2.数据存储过程

--还书修改BorrowDetail表（NonReturnCount -= @returnCount 和 ReturnCount += @returnCount）
--在ReturnBook表中添加信息
if exists(select * from sysobjects where name = 'usp_ReturnBook')
drop procedure usp_ReturnBook
go
Create procedure ups_ReturnBook
@BorrowDetailId int,
@returnCount int,
@AdminName_R varchar(20)
as
	declare @errorSum = 0
	Begin Transaction
	Begin
		insert into ReturnBook(BorrowDetailId,ReturnCount,AdminName_R)
			values(@BorrowDetailId,@ReturnCount,@AdminName_R)
		@errorSum += @@Error
		update BorrowDetail set NonReturnCount -= @returnCount, ReturnCount += @returnCount
			where BorrowDetailId = @BorrowDetailId
		@errorSum += @Error
		if(@errorSum > 0)
			Rollback Transaction
		else
			Commit Transaction
	End
go