USE AdventureWorks2008R2; GO -- Verify that the table does not exist. IF OBJECT_ID (N'my_books', N'U') IS NOT NULL DROP TABLE my_books; GO -- Create table my_books. CREATE TABLE my_books ( Isbn int PRIMARY KEY, Title NVARCHAR(100) ); GO BEGIN TRY BEGIN TRANSACTION; -- This statement will generate an error because the -- column author does not exist in the table. ALTER TABLE my_books DROP COLUMN author; -- If the DDL statement succeeds, commit the transaction. COMMIT TRANSACTION; END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage; -- Test XACT_STATE for 1 or -1. -- XACT_STATE = 0 means there is no transaction and -- a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable. IF (XACT_STATE()) = -1 BEGIN PRINT N'The transaction is in an uncommittable state. ' + 'Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test whether 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
Saturday, April 11, 2015
mssql try catch with transaction commit or rollback using XACT_STATE
Labels:
Mysql VS Mssql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment