Introduction
One of the junior developers asks me a question that there is a table and an associate trigger with this table, If a certain condition fails in the trigger that it rollback the entire transaction.
To understand it let’s take an example
We have a table called [dbo].[tbl_EMPMSTR]
The table contains a trigger named [dbo].[trg_CHECKSAL]
The trigger works like a CHECK constraint. If the salary is <= 500 then it should ROLLBACK TRANSACTION.
Step-1 [ Create the Base Table ]
IF OBJECT_ID(N'dbo.tbl_EMPMSTR', N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPMSTR];
END
GO
CREATE TABLE [dbo].[tbl_EMPMSTR]
(
EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPSAL INT NOT NULL
);
GO
Step-2 [ Create Trigger ]
IF OBJECT_ID(N'dbo.trg_CHECKSAL', N'TR')IS NOT NULL
BEGIN
DROP TRIGGER [dbo].[trg_CHECKSAL];
END
GO
CREATE TRIGGER [dbo].[trg_CHECKSAL]
ON [dbo].[tbl_EMPMSTR]
AFTER INSERT
AS
BEGIN
DECLARE @v_SAL INT = 0;
IF EXISTS(SELECT * FROM inserted)
BEGIN
SET @v_SAL = (SELECT EMPSAL FROM inserted);
IF @v_SAL<= 500
BEGIN
RAISERROR (N'SALARY not Less then the Rs. 500.00', 16, 1);
ROLLBACK TRANSACTION;
END
END
END
GO
Step-3 [ Now Try some Insert Statement ]
INSERT INTO [dbo].[tbl_EMPMSTR]
(EMPNAME, EMPSAL)
VALUES('Chandan Bannerjee', 700);
GO
(1 row(s) affected)
INSERT INTO [dbo].[tbl_EMPMSTR]
(EMPNAME, EMPSAL)
VALUES('Joydeep Das', 300);
Msg 50000, Level 16, State 1, Procedure trg_CHECKSAL, Line 12
SALARY not Less then the Rs. 500.00
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Step-4 [ Observation ]
SELECT * FROM [dbo].[tbl_EMPMSTR];
EMPID EMPNAME EMPSAL
----------- --------------------------- -----------
1 Chandan Bannerjee 700
(1 row(s) affected)
Here the second insert statement which have the employee name JOYDEEP DAS has ROLLBACKED as the salary is less than 500.
Hope you like it.
Posted by: MR. JOYDEEP DAS
