Transactions in SQL Server

Source

Transactions

BEGIN { TRAN | TRANSACTION }        
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
COMMIT [ { TRAN | TRANSACTION }  [ transaction_name | @tran_name_variable ] ] 
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
[ ; ]
ROLLBACK { TRAN | TRANSACTION }         
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]
BEGIN TRY
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE());
UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH

Savepoints

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }   [ ; ]
BEGIN TRAN;
-- Mark savepoint1
SAVE TRAN savepoint1;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com', '555909090');
-- Rollback savepoint1
ROLLBACK TRAN savepoint1;
-- Mark savepoint2
SAVE TRAN savepoint2;
INSERT INTO customers VALUES ('Jeremy', 'Johnsson', 'jeremyjohnsson@mail.com', '555929292');-- Commit the transaction
COMMIT TRAN;

XACT_ABORT and XACT_STATE()

SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', 'dylansmith@mail.com', '555888999');
COMMIT TRAN;
END TRY
BEGIN CATCH
-- Check if there is an open transaction
IF XACT_STATE() <> 0
-- Rollback the transaction
ROLLBACK TRAN;
-- Select the message of the error
SELECT ERROR_MESSAGE() AS Error_message;
END CATCH

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michael Flores

Michael Flores

Data Science student and aspiring Data Analyst