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.
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.