SA0098 : The results from triggers are currently allowed. Consider disabling results from triggers

Introduction

Triggers returning result sets in SQL Server can cause unexpected behavior and inefficient resource utilization.

Description

In SQL Server, a trigger is a special kind of stored procedure that automatically runs when specific changes occur in a database. Allowing triggers to return result sets can cause unexpected complications. This is typically against best practices because triggers are generally meant for data validation or constraint enforcement, not for returning data to applications or users.

For example:

-- Configuring a trigger to return a result set
CREATE TRIGGER trgAfterInsert
ON TableName
AFTER INSERT
AS
BEGIN
    SELECT * FROM Inserted;
END

Triggers like this can lead to issues because:

Unexpected result sets can disrupt application workflows, especially if the application is not designed to handle results from trigger executions.
Returning result sets from triggers can lead to increased bandwidth and memory usage, impacting overall server performance.

How to fix

Disable the option allowing result sets to be returned from triggers to prevent unexpected behavior and resource misuse.

Follow these steps to address the issue:

1.Open SQL Server Management Studio (SSMS) and connect to the appropriate database server.

2.Review existing triggers that might be returning result sets, such as those using a SELECT statement within their BEGIN and END block.

3.Decide whether it is necessary to keep the functionality of these triggers returning result sets. If possible, refactor these triggers to not return data.

4.To prevent triggers from returning result sets, set the server option disallow_results_from_triggers to ON using the following command:

-- Set the option to disallow results from triggers
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'disallow results from triggers', 1;
RECONFIGURE;

For example, refactor the trigger to avoid returning a result set:

-- Before refactoring
CREATE TRIGGER trgAfterInsert
ON TableName
AFTER INSERT
AS
BEGIN
    SELECT * FROM Inserted;
END

-- After refactoring
CREATE TRIGGER trgAfterInsert
ON TableName
AFTER INSERT
AS
BEGIN
    -- Perform necessary operations without returning a result set
END

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

Disallow results from triggers Server Configuration

Analysis Rules