SQL Server Trigger Part 3

Important

Test your DDL triggers to determine their responses to system stored procedure execution. For example, the CREATE TYPE statement and the sp_addtype and sp_rename stored procedures fire a DDL trigger that's created on a CREATE_TYPE event.

For more information about DDL triggers, see DDL Triggers.

DDL triggers don't fire in response to events that affect local or global temporary tables and stored procedures.

Unlike DML triggers, DDL triggers aren't scoped to schemas. So, you can't use functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX for querying metadata about DDL triggers. Use the catalog views instead. For more information, see Get Information About DDL Triggers.

 Note

Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL Triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database.

Logon Triggers

Logon triggers carry out stored procedures in response to a LOGON event. This event happens when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. For more information, see Logon Triggers.

Logon triggers don't fire if authentication fails.

Distributed transactions aren't supported in a logon trigger. Error 3969 returns when a logon trigger that contains a distributed transaction fire.

Disabling a Logon Trigger

A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadminfixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode (-f). For more information, see Database Engine Service Startup Options.

General Trigger Considerations

Returning Results

The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that aren't designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do. To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

Logon triggers always disallow the return of results sets and this behavior isn't configurable. If a logon trigger generates a result set, the trigger fails to launch and the login attempt that fired the trigger is denied.

Multiple Triggers

SQL Server lets you create multiple triggers for each DML, DDL, or LOGON event. For example, if CREATE TRIGGER FOR UPDATE is run for a table that already has an UPDATE trigger, an additional update trigger is created. In earlier versions of SQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.

Recursive Triggers

SQL Server also supports recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

Recursive triggers enable the following types of recursion to occur:

• Indirect recursion

  With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. Trigger T2 then fires and updates table T1.

• Direct recursion

  In direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement runs each TR1 and TR2 one time. Additionally, the launch of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

 Note

The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. There's no defined order in which multiple triggers defined for a specific event are run. Each trigger should be self-contained.

Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

If any one of the triggers carries out a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are run.

Nested Triggers

You can nest triggers to a maximum of 32 levels. If a trigger changes a table on which there's another trigger, the second trigger activates and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. When a Transact-SQL trigger launches managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. Methods invoked from within managed code don't count against this limit.

To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration supports nested triggers. If nested triggers are off, recursive triggers are also disabled, despite the RECURSIVE_TRIGGERS setting that's set by using ALTER DATABASE.

The first AFTER trigger nested inside an INSTEAD OF trigger fires even if the nested triggers server configuration option is 0. But, under this setting, the later AFTER triggers don't fire. Review your applications for nested triggers to determine if the applications follow your business rules when the nested triggers server configuration option is set to 0. If not, make the appropriate modifications.

Deferred Name Resolution

SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don't exist at compile time. This ability is called deferred name res