MS SQL 事务处理规范

 
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