Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

disallow results from triggers Server Configuration Option


Use the disallow results from triggers option to control whether triggers return result sets. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them.

System_CAPS_ICON_important.jpg Important

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

When set to 1, the disallow results from triggers option is set to ON. The default setting for this option is 0 (OFF). If this option is set to 1 (ON), any attempt by a trigger to return a result set fails, and the user receives the following error message:

"Msg 524, Level 16, State 1, Procedure <Procedure Name>, Line <Line#>

"A trigger returned a resultset and the server option 'disallow_results_from_triggers' is true."

The disallow results from triggers option is applied at the Microsoft SQL Server instance level, and it will determine behavior for all existing triggers within the instance.

The disallow results from triggers option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change disallow results from triggers only when show advanced options is set to 1. The setting takes effect immediately without a server restart.

Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)

© 2016 Microsoft