有以下表 Test (name char(12)) name a
b
c
d我写了个存储过程,里面有两个更新语句,目的是要么两个更新语句都成功,要么一个也不成功。
可是结果没达到我的目的。第一条语句成功了,第二条语句不成功。我知道第二条语句为何不成功,
就是不知道为何第二条语句不成功时不回滚,从而达到我的目的。 CREATE PROC TestData
BEGIN TRANSACTION
update test set name='a' where name='c'
update test set name='ggggggggggggggggggggggggggg' where name='d' COMMIT TRANSACTION
go
create table Test (name char(12))insert into test(name)
select 'a' union all
select 'b' union all
select 'c' union all
select 'd';--我写了个存储过程,里面有两个更新语句,目的是要么两个更新语句都成功,
-- 要么一个也不成功。
-- 可是结果没达到我的目的。第一条语句成功了,第二条语句不成功。CREATE PROC TestData
AS
BEGIN
BEGIN TRANSACTION
update test set name='a' where name='c'
update test set name='ggggggggggggggggggggggggggg' where name='d' COMMIT TRANSACTION
END
exec TestData;select * from test;
是少了 begin....end 的原因吗?没有怎么也不提示错误啊。
AS
BEGIN
set xact_abort on
BEGIN TRANSACTION
update test set name='a' where name='c'
update test set name='ggggggggggggggggggggggggggg' where name='d' COMMIT TRANSACTION
END
CREATE PROC TestData
AS
DECLARE @error INT
BEGIN TRAN
BEGIN
update test set name='a' where name='c'
set @error=@@error
update test set name='ggggggggggggggggggggggggggg' where name='d'
set @error=@error+@@error
if @error<>0
ROLLBACK
ELSE
COMMIT
END
GOexec TestData;select * from test;