Nested Triggers and Transactions

I ran across an interesting post on www.sqlteam.com that got me thinking about how transactions interact inside of nested triggers.  Since I'm trying to study how transactions work in SQL Server anyway, I thought I would give this a try.

EXEC sp_configure

BEGIN TRANSACTION TRAN_WRAP

 BEGIN TRANSACTION CREATE_TABLE
  IF (SELECT OBJECT_ID('djl_test_trigger_transactions')) IS NOT NULL
   BEGIN
    DROP TABLE djl_test_trigger_transactions
   END

  CREATE TABLE djl_test_trigger_transactions(
   int_value INT PRIMARY KEY,
   varchar_value VARCHAR(55))
 COMMIT TRANSACTION CREATE_TABLE

 BEGIN TRANSACTION TRIGGER1
  DECLARE @sql NVARCHAR(4000)

  SELECT @sql = '
   CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_1] ON [dbo].[djl_test_trigger_transactions]
   FOR INSERT, UPDATE
   AS
   
   PRINT ''EXECUTING FIRST TRIGGER''

   IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'aaa' + ''')
    BEGIN
    
     IF EXISTS( SELECT * FROM inserted WHERE int_value = 2)
      BEGIN
       RAISERROR(''' + 'Invalid entry.' + ''',16,1)
       ROLLBACK
       RETURN
      END
    END

   IF EXISTS(SELECT int_value FROM inserted WHERE int_value = 1)
    BEGIN

     UPDATE djl_test_trigger_transactions SET varchar_value = ''' + 'aab' + ''' WHERE int_value = 1

    END 

   PRINT ''' + 'ARE YOU STILL ALIVE?' + ''''

  EXEC(@sql)
 
 COMMIT TRANSACTION TRIGGER1
 
 BEGIN TRANSACTION TRIGGER2

  SELECT @sql = '
  CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_2] ON [dbo].[djl_test_trigger_transactions]
  FOR INSERT, UPDATE
  AS
   BEGIN

    PRINT ''EXECUTING SECOND TRIGGER''

    IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'xxx' + ''')
     BEGIN
      INSERT djl_test_trigger_transactions(int_value, varchar_value)
       VALUES(6,''' + 'axs' + ''')
     END

    IF EXISTS(SELECT * FROM inserted WHERE varchar_value = ''' + 'aab' + ''')
     BEGIN

      UPDATE djl_test_trigger_transactions SET int_value = int_value + 12 WHERE varchar_value = ''' + 'aab' + '''

     END
    ELSE
     BEGIN

      INSERT djl_test_trigger_transactions(int_value, varchar_value)
       SELECT MAX(int_value)+2,''axs'' FROM djl_test_trigger_transactions

     END

   END'

  EXEC(@sql)

 COMMIT TRANSACTION TRIGGER2

 BEGIN TRANSACTION TRIGGER_ORDER

  EXEC sp_settriggerorder 'trg_djl_test_trigger_transactions_ins_1', 'first', 'UPDATE'

 COMMIT TRANSACTION TRIGGER_ORDER

COMMIT TRANSACTION TRAN_WRAP
GO

 SELECT * FROM djl_test_trigger_transactions
 PRINT '1,zzz'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(1,'zzz')

 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '2,aaa'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aaa')
GO
 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '4,aaa'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(4,'aaa')

 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '2,aab'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aab')

 SELECT * FROM djl_test_trigger_transactions
GO
 PRINT '12,xxx'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(12,'xxx')
GO
 SELECT * FROM djl_test_trigger_transactions
GO
 PRINT '18,xxx'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(18,'xxx')
GO
 SELECT * FROM djl_test_trigger_transactions
GO


EXEC sp_configure 'nested triggers','0'
RECONFIGURE

BEGIN TRANSACTION TRAN_WRAP

 BEGIN TRANSACTION CREATE_TABLE
  IF (SELECT OBJECT_ID('djl_test_trigger_transactions')) IS NOT NULL
   BEGIN
    DROP TABLE djl_test_trigger_transactions
   END

  CREATE TABLE djl_test_trigger_transactions(
   int_value INT PRIMARY KEY,
   varchar_value VARCHAR(55))
 COMMIT TRANSACTION CREATE_TABLE

 BEGIN TRANSACTION TRIGGER1
  DECLARE @sql NVARCHAR(4000)

  SELECT @sql = '
   CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_1] ON [dbo].[djl_test_trigger_transactions]
   FOR INSERT, UPDATE
   AS
   
   PRINT ''EXECUTING FIRST TRIGGER''

   IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'aaa' + ''')
    BEGIN
    
     IF EXISTS( SELECT * FROM inserted WHERE int_value = 2)
      BEGIN
       RAISERROR(''' + 'Invalid entry.' + ''',16,1)
       ROLLBACK
       RETURN
      END
    END

   IF EXISTS(SELECT int_value FROM inserted WHERE int_value = 1)
    BEGIN

     UPDATE djl_test_trigger_transactions SET varchar_value = ''' + 'aab' + ''' WHERE int_value = 1

    END 

   PRINT ''' + 'ARE YOU STILL ALIVE?' + ''''

  EXEC(@sql)
 
 COMMIT TRANSACTION TRIGGER1
 
 BEGIN TRANSACTION TRIGGER2

  SELECT @sql = '
  CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_2] ON [dbo].[djl_test_trigger_transactions]
  FOR INSERT, UPDATE
  AS
   BEGIN

    PRINT ''EXECUTING SECOND TRIGGER''

    IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'xxx' + ''')
     BEGIN
      INSERT djl_test_trigger_transactions(int_value, varchar_value)
       VALUES(6,''' + 'axs' + ''')
     END

    IF EXISTS(SELECT * FROM inserted WHERE varchar_value = ''' + 'aab' + ''')
     BEGIN

      UPDATE djl_test_trigger_transactions SET int_value = int_value + 12 WHERE varchar_value = ''' + 'aab' + '''

     END
    ELSE
     BEGIN

      INSERT djl_test_trigger_transactions(int_value, varchar_value)
       SELECT MAX(int_value)+2,''axs'' FROM djl_test_trigger_transactions

     END

   END'

  EXEC(@sql)

 COMMIT TRANSACTION TRIGGER2

 BEGIN TRANSACTION TRIGGER_ORDER

  EXEC sp_settriggerorder 'trg_djl_test_trigger_transactions_ins_1', 'first', 'UPDATE'

 COMMIT TRANSACTION TRIGGER_ORDER

COMMIT TRANSACTION TRAN_WRAP
GO

 SELECT * FROM djl_test_trigger_transactions
 PRINT '1,zzz'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(1,'zzz')

 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '2,aaa'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aaa')
GO
 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '4,aaa'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(4,'aaa')
GO
 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '2,aab'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aab')

 SELECT * FROM djl_test_trigger_transactions

GO
 PRINT '12,xxx'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(12,'xxx')
GO
 SELECT * FROM djl_test_trigger_transactions
GO
 PRINT '18,xxx'
 INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(18,'xxx')
GO
 SELECT * FROM djl_test_trigger_transactions
GO

EXEC sp_configure 'nested triggers','1'
RECONFIGURE

Results:
 1.  The nested triggers option does not affect the ability to roll back an entire transaction.
 2.  The nested triggers run in order if you have set the order, regardless of whether nested triggers are on or not.
 3.  ROLLBACK rolls back the work already done in the batch. 
 4.  RETURN stops the batch from processing any further.  Otherwise, the trigger would finish to completion.
 5.  RAISERROR is informational.
 6.  Any fatal errors in the trigger transaction space, such as primary key duplicates, results in a complete immediate rollback and stop of the batch.

Let me know if I have missed anything or if you have something to add.  I'm trying to put together an article that explains in detail how transactions are handled in SQL Server.  So far, I have pieces in progress or planned on nested transactions, transactions in cursors and loops, error handling, distributed transactions, transaction considerations for application architecture, and a piece on transaction logic flow dealing with transaction nesting logic and use of the return state to make control transaction flow properly and be carried through multiple steps in the application or job.  If anyone wants to see anything else, let me know.  I'm trying to have it done by next week, but if I get good feedback or find out I'm totally wrong about some things I'll push it back.  (The totally wrong part is more likely to happen then anything.)

Favorite words used: 0 (WOW)

Mean level (1-10):  0 (I'm ruined.)

Education level (1-10):  3 (I learned something from this.  I'm not sure if anyone else will, but it's fun to test concepts.

Entertainment level (1-10): 1 (If you are a hopeless nerd, you should find this to be mildly entertaining.  I did anyway.)

 

Print | posted on Tuesday, March 30, 2004 7:48 PM

Comments on this post

# re: Nested Triggers and Transactions

Requesting Gravatar...
This is not related to your post...

"Nonintelligable" is not a word. I suspect you mean "unintelligable"
Left by Tonks on Sep 13, 2004 6:55 PM

# Tegel's Blog Summary

Requesting Gravatar...
I believe I've already created trackbacks for this. I love the summaries he gives though. It's a good place to start reading the daily blogs from.
Left by Pingback/TrackBack on Dec 10, 2005 12:45 PM
Comments have been closed on this topic.