工作中有一项业务,需要在存储过程里先把A表的数据插入到B表,然后再删除A表的数据。
insert into B select * from A--因为文章重点不是SQL语句,所以就简化成这两句话了
delete A
后来有发生过A,B两张表都没有数据的情况,当时挺奇怪,因为有添加事务,出错了应该会回滚数据,后来查阅资料才发现,还需要加上这一段SET XACT_ABORT ON
数据库默认XACT_ABORT为OFF,大意是:“在某些情况下,仅回滚引发错误的Transact-SQL语句并继续处理事务”
也就是说,只回滚事务里报错的那一段SQL语句,但后续的SQL语句仍然会执行。所以问题原因可能是,A表向B表插入数据的这一段insert into B select * from A出错了,可能当时是锁表了,然后回滚了这一句话,但事务仍然继续往下执行了delete A,所以导致A,B表都没有数据
而XACT_ABORT为ON时:“SQL语句引发运行时错误,则终止并回滚整个事务”,也就是说,如果insert into B select * from A这段话出错了,就不会继续往下执行delete A这段话了,并且回滚整个事务而不仅仅是出错的那一段
SET XACT_ABORT ON--设置XACT_ABORT为ON
Begin Transaction--开启事务
IF @@Error <> 0 GOTO PROBLEM
insert into B select * from A
IF @@Error <> 0 GOTO PROBLEM
delete A
Commit Transaction--无异常则提交事务
Return 0
PROBLEM:
Print '错误'
Rollback Transaction--有异常回滚事务
Return 1