BEGIN TRY
BEGIN TRANSACTION;
-- 需要执行的SQL语句
ALTER TABLE my_books DROP COLUMN author;
-- 如果执行成功,结束事务
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage;
-- 检查 XACT_STATE for 1 or -1 指示会话是否具有活动事务以及是否可以提交事务.
-- XACT_STATE = 1 means 会话具有活动事务。会话可以执行任何操作,包括写入数据和提交事务
-- XACT_STATE = 0 means 会话没有活动事务
-- XACT_STATE = -1 means 会话无法提交事务或回滚到保存点.
IF (XACT_STATE()) = -1
BEGIN PRINT N'The transaction is in an uncommittable state. ' + 'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable. ' + 'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO