Logon Trigger Execution Guidelines
New: 12 December 2006
This topic discusses guidelines for specifying the firing order of logon triggers, and managing transactions inside logon triggers.
Specifying First and Last Trigger
Multiple triggers can be defined on the LOGON event. Any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure. SQL Server does not guarantee the execution order of the remaining triggers. For more information, see Specifying First and Last Triggers.
Managing Transactions
Before SQL Server fires a logon trigger, SQL Server creates an implicit transaction that is independent from any user transaction. Therefore, when the first logon trigger starts firing, the transaction count is 1. After all the logon triggers finish executing, the transaction commits. As with other types of triggers, SQL Server returns an error if a logon trigger finishes execution with a transaction count of 0. The ROLLBACK TRANSACTION statement resets the transaction count to 0, even if the statement is issued inside a nested transaction. COMMIT TRANSACTION might decrement the transaction count to 0. Therefore, we advise against issuing COMMIT TRANSACTION statements inside logon triggers.
Consider the following when you are using a ROLLBACK TRANSACTION statement inside logon triggers:
- Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back. These modifications include those made by the current trigger and those made by previous triggers that executed on the same event. Any remaining triggers for the specific event are not executed.
- The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.
A user session is not established if any one of the following conditions occur during execution of a trigger on a LOGON event:
- The original implicit transaction is rolled back or fails.
- An error that has severity greater than 20 is raised inside the trigger body.
See Also
Concepts
Logon Triggers
Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION
Designing and Creating Databases