SQL Server Rollback, Transaction Management and Exception Handling Example


USE test; –database name
GO

IF (OBJECT_ID(‘[test].[dbo].[Employees]’) IS NULL) –if the table was not created before create it
CREATE TABLE [test].[dbo].[Employees](
[employee_id] [int] NOT NULL,
[employeee_name] [varchar](50) NULL,
[employee_surname] [varchar](50) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
([employee_id] ASC
)

)
BEGIN TRAN INSERT_EMPLOYEE
BEGIN

INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(1,’Test Employee Name 1′, ‘Test Employee Surname 1′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(2,’Test Employee Name 2’, ‘Test Employee Surname 2′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(3,’Test Employee Name 3’, ‘Test Employee Surname 3′);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(4,’Test Employee Name 4’, ‘Test Employee Surname 4’);
INSERT INTO Employees (employee_id,employeee_name,employee_surname)
VALUES(4,’Test Employee Name 4′, ‘Test Employee Surname 4’); –trying to add the same employee again causing an exception

IF @@ERROR != 0 –check @@ERROR variable and Rollback the whole trancation if an error occurs
BEGIN
ROLLBACK TRAN INSERT_EMPLOYEE –rollback the whole transaction (all INSERT statements)
END
ELSE
BEGIN
COMMIT TRAN INSERT_EMPLOYEE; –Commit the transaction if the operation is successful
END
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s