SA0013 : Avoid returning results in triggers

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:

 1CREATE TRIGGER trgAfterUpdate
 2ON YourTable
 3AFTER UPDATE
 4AS
 5BEGIN
 6    -- Problematic PRINT statement
 7    PRINT 'Trigger executed.'
 8
 9    -- Problematic SELECT statement
10    SELECT * FROM inserted;
11END;

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.

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:

1-- Corrected trigger without output
2CREATE TRIGGER trgAfterUpdate
3ON YourTable
4AFTER UPDATE
5AS
6BEGIN
7    -- Removed problematic statements to ensure no data is returned
8    -- Implement necessary logic here
9END;

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

The rule does not need Analysis Context or SQL Connection.

Performance Rules, Bugs

There is no additional info for this rule.

Other Resources