Commit Command
Commit Command is used to permanently save any transaction into database.
Systax:
Commit;
Rollback Command
This command restores the database to last commited state. It is also use with savepoint command to
Jump to a save point in a transaction.
Syntax:
Rollback to Savepoint-name;
SavePoint Command
Savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.
Systax:
Savepoint savepoint-name;
Example
CREATE TABLE Employee
(
Name VARCHAR(30)
)
BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)
INSERT INTO Employee VALUES ('Tom')
SAVE TRAN Savepoint1
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)
INSERT INTO Employee VALUES ('Dick')
ROLLBACK TRAN Savepoint1
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)
COMMIT TRAN
PRINT 'Complete: ' + CONVERT(VARCHAR,@@TRANCOUNT)