带事务的存储过程(简单模板)
--@@ERROR ,如果前一个Transact-SQL 语句执行没有错误,则返回 0
--Set NOCOUNT ON,防止将会话中每一条语句所影响的行数消息发回给请求的客户机
--SET XACT_ABORT ON,当 Transact-SQL 语句产生运行时错误,自动回滚当前事务
IF EXISTS(SELECT * FROM sysobjects WHERE NAME='procEditCapacity' AND TYPE='p')
DROP PROCEDURE proEditCapacity
GO
CREATE PROC proEditCapacity
@fileCapacity int, --文件容量
@albumCapacity int, --文件容量
@ID int --ID
AS
SET XACT_ABORT ON
BEGIN TRAN --事务开始
UPDATE n0_userLevel SET n0_usLevFileCapacity=@fileCapacity,n0_usLevAlbumCapacity=@albumCapacity WHERE n0_userLevelID=@ID
IF @@ERROR <>0 GOTO lb_err
UPDATE n0_userExtend SET n0_usLevFileCapacity=@fileCapacity
WHERE n0_userID=(SELECT n0_userID FROM n0_user WHERE n0_userLevelID=@ID)
IF @@ERROR <>0 GOTO lb_err
COMMIT TRAN --事务提交
RETURN
lb_err:
ROLLBACK TRAN --事务回滚
GO