Nested Triggers and Transactions

Tue Mar 30, 2004 by Derrick Leggett

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 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 * 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.)

Legacy Comments


Tonks 2004-09-13	re: Nested Triggers and Transactions This is not related to your post... "Nonintelligable" is not a word. I suspect you mean "unintelligable"