Trigger Specifics - Nested and Recursive Triggers

(Page 2 of 4 )

A trigger can fire other triggers on the same or other tables when it inserts, updates, or deletes records in them. This technique is called nesting triggers.

If a trigger changes records in its own table, it can fire another instance of itself. Such an invocation is called direct invocation of recursive triggers.

There is another scenario in which invocation of recursive triggers might occur. The trigger on one table might fire a trigger on a second table. The trigger on the second table might change the first table again, and the first trigger will fire again. This scenario is called indirect invocation of recursive triggers.

All these scenarios might be ideal for implementing referential integrity and business rules, but they might also be too complicated to design, understand, and manage. If you are not careful, the first trigger might call the second, then the second might call the first, then the first the second, and so on.

Very often, the SQL Server environment is configured to prevent this kind of behavior. To disable nested triggers and recursive triggers, you need to use the stored procedure sp_configure to set the Nested Triggers server option, and then use the Alter Table statement to set the Recursive_Triggers option to Off mode. Keep in mind that recursive triggers will be disabled automatically if you disable nested triggers.

After Trigger Restrictions

The following are the trigger restrictions, none of which usually causes any difficulties:

• The trigger must be created with the first statement in a batch.
  
• The name of the trigger is its Transact-SQL identifier, and therefore must be no more than 128 characters long.
  
• The trigger’s name must be unique in the database.
  
• A trigger can only be associated with one table, but one table can have many triggers. In the past, only one trigger could be associated with one modification statement on one table. Now, each required function can be implemented in a separate trigger. By implementing these features in separate triggers, you assure that the triggers will be easier to understand and manage.
  
• Triggers cannot be nested to more than 32 levels, nor can they be invoked recursively more than 32 times. Attempting to do so causes SQL Server to return an error.
  
• A trigger must not contain any of the following Transact-SQL statements:
  
  Alter Database          Drop Database
  
  Alter Procedure         Drop Default
  
  Alter Table               Drop Index
  
  Alter Trigger             Drop Procedure
  
  Alter View                Drop Rule
  
  Create Database       Drop Table
  
  Create Default          Drop Trigger
  
  Create Index            Drop View
  
  Create Procedure      Grant
  
  Create Rule              Load Database
  
  Create Schema         Load Log
  
  Create Table            Reconfigure
  
  Create Trigger          Restore Database
  
  Create View             Restore Log
  
  Deny                      Revoke
  
  Disk Init                  Truncate Table
  
  Disk Resize              Update Statistics
  
• You cannot reference text, Ntext, or image columns in triggers, but there are no problems with referencing varchar(max), Nvarchar(max), and varbinary(max) columns:
  
  CREATE TRIGGER dbo.trOrderHeader_D
  ON dbo.OrderHeader
  AFTER DELETE
  AS
  BEGIN
  SET NOCOUNT ON
  
  -- collect OrderItems and Orders in OrderDeleted
  INSERT INTO [dbo].[OrderDeleted] 
      ([OrderId],[OrderDate],[RequestedById]
      ,[TargetDate],[CompletionDate],[DestinationLocationId]
      ,[Note],[OrderTypeId],[OrderStatusid]
      ,[UserName],[ChangeDT])  
  SELECT [OrderId],[OrderDate],[RequestedById]
      ,[TargetDate],[CompletionDate],[DestinationLocationId]
      ,[Note],[OrderTypeId],[OrderStatusid]
      , SUSER_SNAME(), GETDATE()
  FROM deleted
  
  END

Next: Instead-of Triggers >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Trigger Specifics

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.