Can we return Results in Triggers?

Yes, you can return results from trigger. There are three kinds of return values that are interesting:

·         Result sets

·         Raiserror messages

·         Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases.  It is never a good idea to return data from a trigger, but it is actually pretty useful for testing.

LOGON triggers always disallow results sets to be returned, and this behavior is not configurable. If a LOGON trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

We are enabling an advance feature ‘disallow results from triggers’ to 1 to enable this feature in the earlier version of SQL Server and it is also recommended by SQL Server to make it 1.

sp_configure 'disallow results from triggers', 1

GO

RECONFIGURE

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.

Note: 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 are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this.