IF OBJECT_ID('bankInfo') IS NOT NULL
DROP TABLE bankInfo
GO
--银行卡信息表
CREATE TABLE bankInfo
(
uId VARCHAR(18) NOT NULL PRIMARY KEY,--卡号
uName VARCHAR(20), --卡主姓名
uMoney MONEY --卡中余额
)
GO
INSERT INTO bankInfo VALUES('9559901','张三',1)
INSERT INTO bankInfo VALUES('9559902','李四',1000)
INSERT INTO bankInfo VALUES('9559903','王五',800)
INSERT INTO bankInfo VALUES('9559904','赵六',50)
INSERT INTO bankInfo VALUES('9559905','赵明',5)
SELECT * FROM bankInfo
------begin transaction---------开始事务
SET IMPLICIT_TRANSACTIONS ON
DECLARE @myerror INT --用于存储错误号
SET @myerror=0
SET @myerror=@myerror+(select count(*) from bankInfo WHERE uId='9559902' and uMoney<800) --如果帐号余额小于800,则将error+1
UPDATE bankInfo SET uMoney=uMoney-800 WHERE uId='9559902'
SET @myerror=@myerror+@@ERROR --如果产生错误,则将错误号累加
UPDATE bankInfo SET uMoney=uMoney+800 WHERE uId='9559901'
SET @myerror=@myerror+@@ERROR --如果产生错误,则将错误号累加
IF @myerror>0 --如果执行过程出错
BEGIN
PRINT '转账失败,正准备回滚事务!'
ROLLBACK TRANSACTION
END
ELSE --如果过程中没有出现问题
BEGIN
PRINT '转账成功,准备提交事务!'
COMMIT TRANSACTION
END
------end transaction---------结束事务
PRINT '转账后的余额'
SELECT * FROM bankInfo
DROP TABLE bankInfo
GO
--银行卡信息表
CREATE TABLE bankInfo
(
uId VARCHAR(18) NOT NULL PRIMARY KEY,--卡号
uName VARCHAR(20), --卡主姓名
uMoney MONEY --卡中余额
)
GO
INSERT INTO bankInfo VALUES('9559901','张三',1)
INSERT INTO bankInfo VALUES('9559902','李四',1000)
INSERT INTO bankInfo VALUES('9559903','王五',800)
INSERT INTO bankInfo VALUES('9559904','赵六',50)
INSERT INTO bankInfo VALUES('9559905','赵明',5)
SELECT * FROM bankInfo
------begin transaction---------开始事务
SET IMPLICIT_TRANSACTIONS ON
DECLARE @myerror INT --用于存储错误号
SET @myerror=0
SET @myerror=@myerror+(select count(*) from bankInfo WHERE uId='9559902' and uMoney<800) --如果帐号余额小于800,则将error+1
UPDATE bankInfo SET uMoney=uMoney-800 WHERE uId='9559902'
SET @myerror=@myerror+@@ERROR --如果产生错误,则将错误号累加
UPDATE bankInfo SET uMoney=uMoney+800 WHERE uId='9559901'
SET @myerror=@myerror+@@ERROR --如果产生错误,则将错误号累加
IF @myerror>0 --如果执行过程出错
BEGIN
PRINT '转账失败,正准备回滚事务!'
ROLLBACK TRANSACTION
END
ELSE --如果过程中没有出现问题
BEGIN
PRINT '转账成功,准备提交事务!'
COMMIT TRANSACTION
END
------end transaction---------结束事务
PRINT '转账后的余额'
SELECT * FROM bankInfo