T-SQL: Error Handling within Triggers

  • Article

Introduction

The goal of this article is to provide a simple and easy to use error handling mechanism within triggers context. This article is completely compatible with SQL Server 2012 and 2014.

Problem Definition

Triggers are strange objects that have their own rules!

  • The first rule says that triggers are part of the invoking transaction (the transaction that fired them). Yes, this is True and it means that at the beginning of the trigger, both values of @@trancount and xact_state() are "1". So, if we use COMMIT or ROLLBACK inside the trigger, their values will change to "0" just after executing these statements. 
  • The second strange rule is that if the transaction ended in the trigger, the database raises an abortion error. An example of this rule is executing COMMIT or ROLLBACK within the trigger. 

Next code shows these rules:

-- create test table
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
  DROP TABLE  dbo.Test ;
GO
CREATE TABLE  dbo.Test
  ( Id INT  IDENTITY PRIMARY KEY,
    NAME NVARCHAR(128)
  ) ;
GO
-- create test trigger
CREATE TRIGGER  dbo.TriggerForTest
   ON  dbo.Test
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
     
    -- declare variables
    DECLARE @trancount CHAR(1) , 
            @XACT_STATE CHAR(1) ;
     
    -- fetch and print values at the beginning of the trigger
    SET @trancount =  @@TRANCOUNT ;
    SET @XACT_STATE  = XACT_STATE() ;
    PRINT '------------------------------------------------------------------------' ;
    PRINT 'When trigger starts @@trancount value is (' + @trancount + ' ).';
    PRINT 'When trigger starts XACT_STATE() return value is (' + @XACT_STATE + ' ).';
    PRINT '------------------------------------------------------------------------' ;
 
    -- ending the transaction inside the trigger
    COMMIT TRAN ;
 
    -- fetch and print values again
    SET @trancount =  @@TRANCOUNT ;
    SET @XACT_STATE  = XACT_STATE() ;
    PRINT 'After executing COMMIT statement, @@trancount value is (' + @trancount + ' ).';
    PRINT 'After executing COMMIT statement, XACT_STATE() return value is (' + @XACT_STATE + ' ).';
    PRINT '------------------------------------------------------------------------' ;
 
END ;
GO
-- test time!
INSERT dbo.Test ( Name )
  VALUES  ( N'somthing'  ) ;

Figure 1

So, what is the Error Handling mechanism within Triggers?

Solution

There can be two types of solution

Classic Solution

This solution uses the second rule to rollback trigger and raise an error. The following code shows this mechanism:

-- create test table
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
  DROP TABLE  dbo.Test ;
GO
CREATE TABLE  dbo.Test
  ( Id INT  IDENTITY PRIMARY KEY,
    NAME NVARCHAR(128)
  ) ;
GO
-- create test trigger
CREATE TRIGGER  dbo.TriggerForTest
   ON  dbo.Test
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
     
    IF 1 = 1
      BEGIN
        -- rollback and end the transaction inside the trigger    
        ROLLBACK TRAN ;
 
        -- raise an error
        RAISERROR ( 'Error Message!', 16, 1) ;
      END
END ;
GO
-- test time!
INSERT dbo.Test ( Name )
  VALUES  ( N'somthing'  ) ;

Figure 2

Pitfall

This solution works fine until the RAISERROR is the last statement in trigger. If we have some statements after RAISERROR, they will execute as shown in next code:

-- create test table
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
  DROP TABLE  dbo.Test ;
GO
CREATE TABLE  dbo.Test
  ( Id INT  IDENTITY PRIMARY KEY,
    NAME NVARCHAR(128)
  ) ;
GO
-- create test trigger
CREATE TRIGGER  dbo.TriggerForTest
   ON  dbo.Test
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
     
    IF 1 = 1
      BEGIN
        -- rollback and end the transaction inside the trigger    
        ROLLBACK TRAN ;
 
        -- raise an error
        RAISERROR ( 'Error Message!', 16, 1) ;
      END
   
    INSERT dbo.Test ( Name )
      VALUES  ( N'extra'  ) ;        
END ;
GO
-- test time!
INSERT dbo.Test ( Name )
  VALUES  ( N'somthing'  ) ;
GO
SELECT * 
FROM dbo.Test

Figure 3

Modern Solution

This solution is applicable to SQL Server 2012 and above versions. THROW statement enhances the error handling in triggers.  It rollback the statements and throw an error message. Next code shows this mechanism:

-- create test table
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
  DROP TABLE  dbo.Test ;
GO
CREATE TABLE  dbo.Test
  ( Id INT  IDENTITY PRIMARY KEY,
    NAME NVARCHAR(128)
  ) ;
GO
-- create test trigger
CREATE TRIGGER  dbo.TriggerForTest
   ON  dbo.Test
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
     
    IF 1 = 1
      -- just throw!
      THROW 60000, 'Error Message!', 1 ;
 
END ;
GO
-- test time!
INSERT dbo.Test ( Name )
  VALUES  ( N'somthing'  ) ;
GO
SELECT * 
FROM dbo.Test ;

Figure 4

Conclusion

As I explained in former article, introducing the THROW statement was a revolutionary movement in SQL Server 2012 Error Handling. This article proves it again, this time with triggers. 

See Also

Other Languages