This article shows how to use TCL (Transaction Control Language) statements.
The T-SQL TCL statements are used to manage transactions in the SQL Server database.
TCL statements consist of the following T-SQL statements: COMMIT, ROLLBACK, SAVEPOINT.
COMMIT
The T-SQL COMMIT statement specifies the end of a successful transaction.
USE model;
GO
BEGIN TRANSACTION;
GO
DELETE FROM books WHERE id = 10;
GO
UPDATE books SET name='Transact-SQL' WHERE id = 9;
GO
insert into books(id, name, price)
values (10,'SQL Server', 90);
GO
COMMIT TRANSACTION;
GO
ROLLBACK
The T-SQL ROLLBACK statement is used to rollback a transaction to the start of the transaction, or to a savepoint.
USE model;
GO
BEGIN TRANSACTION;
GO
UPDATE courses SET price=30 WHERE id = 5;
GO
insert into courses(id, name, price)
values (7,'Medicine', 190);
GO
ROLLBACK TRANSACTION;
GO
SAVEPOINT
The T-SQL SAVEPOINT statement is used to set a save point within a transaction.
USE model;
GO
DECLARE @Counter INT;
SET @Counter = @@TRANCOUNT;
IF @Counter > 0
SAVE TRANSACTION my_savepoint;
ELSE
BEGIN TRANSACTION;
GO
insert into courses(id,name, price)
values(7,'Medicine',190);
GO
COMMIT TRANSACTION;
GO