SA0013 : Avoid returning results in triggers

Introduction

Returning data from triggers is not recommended because applications modifying tables or views typically do not expect results, which can lead to unexpected behavior.

Description

Triggers in SQL Server are special types of stored procedures that automatically execute in response to specific events on a table or view. One common problem is when these triggers inadvertently send results back to the application or user that invoked them. This can cause unexpected behavior in applications that do not expect results to be returned from data modification operations. Therefore, it is crucial to ensure that triggers do not include statements like PRINT , SELECT (without assignment or INTO clause), or FETCH (without assignment) that return data.

Example of a trigger returning data:

CREATE TRIGGER trgAfterUpdate
ON YourTable
AFTER UPDATE
AS
BEGIN
    -- Problematic PRINT statement
    PRINT 'Trigger executed.'

    -- Problematic SELECT statement
    SELECT * FROM inserted;
END;

This example is problematic because:

The PRINT statement outputs text back to the client, confusing applications expecting silent operations.
The SELECT statement returns data to the caller, which can disrupt application logic by introducing unexpected result sets.

How to fix

To ensure that triggers do not inadvertently return data to the caller, modify the trigger to suppress output operations.

Follow these steps to address the issue:

1.Identify and remove any PRINT , SELECT (without INTO clause), or FETCH (without assignment) statements within the trigger that cause output to be returned to the caller.

2.If the application relies on the trigger returning results, refactor the application logic to eliminate this dependency. This can include retrieving necessary data directly through separate queries rather than using the trigger.

3.Validate that the modified trigger maintains the intended business logic, focusing on performing its task silently without returning any data to the caller.

For example, modify the trigger as shown below:

-- Corrected trigger without output
CREATE TRIGGER trgAfterUpdate
ON YourTable
AFTER UPDATE
AS
BEGIN
    -- Removed problematic statements to ensure no data is returned
    -- Implement necessary logic here
END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
AllowPrint	The parameter specifies if using PRINT statement inside triggers will be allowed.	no

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TRIGGER trg_Test_SA0013
ON dbo.TestTable
AFTER INSERT
AS
BEGIN
    PRINT 'Row inserted into TestTable';

    -- Returning results using SELECT
    SELECT * FROM inserted;

    -- Using OUTPUT to return inserted rows
    INSERT INTO dbo.TestLog (InsertedID)
    OUTPUT inserted.ID
    SELECT ID FROM inserted;
END

Analysis Results

	Message	Line	Column
1	SA0013 : Avoid returning results in triggers.	6	4
2	SA0013 : Avoid returning results in triggers.	9	4
3	SA0013 : Avoid returning results in triggers.	13	4

Analysis Rules