Transactions are the part
of Transaction Control Language (TCL) Commands in SQL Server. They are used to
manage transactions in database. These are used to manage the changes made by
DML statements. COMMIT and ROLLBACK are key concepts in the
transactions, Commit are used to save the whole transaction. Rollback is used
to undo all the changes made by the DML in current transaction.
Example Procedure :
CREATE PROCEDURE P_INS_DATA_LOG
(
@VAL1 VARCHAR(4)=null,
@VAL2 VARCHAR(10)=null,
@VAL3 VARCHAR(10)=null,
@VAL4 VARCHAR(10)=null,
@VAL5 VARCHAR(15)=null
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN
INSERT INTO
TABLE1 (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5)
VALUES
(@VAL1,@VAL2,@VAL3,@VAL4,@VAL5)
IF(@@ERROR <> 0)
GOTO QUITWITHROLLBACK
ELSE
GOTO
ENDSAVE
END
QUITWITHROLLBACK:
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION
END
ENDSAVE:
IF (@@TRANCOUNT > 0)
BEGIN
COMMIT TRANSACTION
END
END
GO