Friday 29 June 2012

COMMIT and ROLLBACK TRANSACTION in Triggers


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