One of my friends asks me to write an article related to COMMIT and ROLLBACK TRANSACTION in Triggers.
After reading one of my previous article
DELETE restriction of VIEWS
Where I am using a ROLLBACK TRAN in a trigger (INSTEAD OF DELETE trigger), he had a question on mid that without using the BEGIN TRANSACTION on the Trigger, can we us the ROLLBACK TRANSACTION. It is better, if you read my previous article to understand the complete scenario.
The executions of COMMIT TRANSACTION and ROLLBACK TRANSACTION inside the trigger are possible.
When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated.
If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.
However, in the case of TRIGGER, if BEGIN TRANSACTION is executed before or after the COMMIT TRANSACTION, the error does not occur.
In another scenario of TRIGGER, if BEGIN TRANSACTION is executed after the ROLLBACK TRANSACTION, the error does not occur.
Now I am given an example to illustrate my points. Here I am taking my previous article example to demonstrate it.
IF OBJECT_ID('emp_data') IS NOT NULL
BEGIN
DROP TABLE emp_data
END
GO
CREATE TABLE emp_data
(
IDNO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
CONSTRAINT CHK_emp_data_EMPGRADE
CHECK (EMPGRADE IN('A','B','C'))
)
GO
-- Insert records
INSERT INTO emp_data
(EMPNAME, EMPGRADE)
VALUES ('Sudip Das', 'A'),
('Joydeep Das', 'B'),
('Sangram Jit', 'B'),
('Tuhin Shinah', 'B'),
('Bhola', 'C')
-- Display records
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
-- Output
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
-- Creation of VIEWS
IF OBJECT_ID('view_emp_data') IS NOT NULL
BEGIN
DROP VIEW view_emp_data
END
GO
CREATE VIEW view_emp_data
AS
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data
GO
-- Display the Records of Views
SELECT IDNO, EMPNAME, EMPGRADE
FROM view_emp_data
-- Output from View
IDNO EMPNAME EMPGRADE
1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C
Now I am creating the trigger with simple ROLLBACK TRANSACTION without any BEGIN TRANSACTION.
IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
BEGIN
DROP TRIGGER trg_RestrictDEL
END
GO
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )
ROLLBACK TRANSACTION
END
END
GO
Now I am trying to DELETE records
DELETE view_emp_data
WHERE IDNO=3
Please Look at the Error Message. The Error portion is highlighted here.
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Now I am trying to ALTER this Trigger with BIGIN TRANSACTION, immediate after the ROLLBACK TRANSACTION. We find no Error after execution. (Please try this)
IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
BEGIN
DROP TRIGGER trg_RestrictDEL
END
GO
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
BEGIN TRAN
RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )
ROLLBACK TRANSACTION
BEGIN TRANSACTION
END
END
GO
Now execute the DELETE statement again.
DELETE view_emp_data
WHERE IDNO=3
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!
No error message.
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment