disallow results from triggers Server Configuration Option
Applies to: SQL Server (all supported versions)
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.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 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.