34,837
社区成员




SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
insert into testtb(id)values('d');
COMMIT TRAN
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
exec('insert into testtb(id)values(''d'')');
COMMIT TRAN
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
if @@error<>0
rollback tran
exec('insert into testtb(id)values(''d'')');
if @@error<>0
rollback tran
COMMIT TRAN
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
if @@error<>0
rollback tran
exec('insert into testtb(id)values(''d'')');
if @@error<>0
rollback tran
COMMIT TRAN
SET XACT_ABORT ON
BEGIN TRAN
...
COMMIT TRAN
1.SET XACT_ABORT 只对当前作用域起作用
2.SET XACT_ABORT 只对运行时错误起作用,对编译错误不起作用 insert into testtb(id)values('d');这条语句运行时产生语法错误(属编译错误)所以不起作用
CREATE TABLE testtb(id int)
go
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
exec('insert into');
COMMIT TRAN
go
select * from testtb
/*
id
-----------
(0 row(s) affected)
*/
drop table testtb
if @@error <>0
begin
rollback tran
raiserror('唤起错误提示',16,1)
end
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
exec('insert into');
COMMIT TRAN
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
exec('insert into testtb(id)values(''d'')');
COMMIT TRAN
--前两种写法可以全部回滚的。
CREATE TABLE testtb(id int)
go
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
insert into testtb(id)values('d');
COMMIT TRAN
go
select * from testtb
/*
id
-----------
(0 row(s) affected)
*/
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
exec('insert into testtb(id)values(''d'')');
COMMIT TRAN
go
select * from testtb
/*
id
-----------
(0 row(s) affected)
*/
drop table testtb
exec('
SET XACT_ABORT ON
BEGIN TRAN
insert into testtb(id) values(1);
insert into testtb(id)values(''d'');
COMMIT TRAN
')