Click or drag to resize

SA0013 : Avoid returning results in triggers

The topic describes the SA0013 analysis rule.

Message

Avoid returning results in triggers

Description

This rule scans triggers to ensure they do not send data back to the caller.

Applications that modify tables or views with triggers do not necessarily expect results to be returned as part of the modification operation. For this reason it is not recommended to return results from within triggers.

This rule flags as not recommended the use of the following statements within a trigger:

SQL
1PRINT statement
2SELECT (without assignment or INTO clause)
3FETCH (without assignment)

Scope

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

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

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
  1ALTER TRIGGER [TRG_SqlEnlightTest_SA0013]
  2ON DATABASE
  3FOR ddl_database_level_events
  4AS 
  5BEGIN
  6    SET nocount ON;
  7
  8    DECLARE @data AS xml;
  9    DECLARE @schema AS  sysname;
 10    DECLARE @object AS  sysname;
 11    DECLARE @eventType AS  sysname;
 12
 13    SET @data=EVENTDATA();
 14
 15    SET @eventType=@data.value( '(/EVENT_INSTANCE/EventType)[1]',
 16                                'sysname');
 17
 18    SET @schema=@data.value( '(/EVENT_INSTANCE/SchemaName)[1]',
 19                             'sysname');
 20
 21    SET @object=@data.value( '(/EVENT_INSTANCE/ObjectName)[1]',
 22                             'sysname')
 23
 24    IF @object IS NOT NULL
 25    BEGIN
 26                -- The PRINT statement will be ignored and will not cause rule violation if the AllowPrint rule parameter is set to 'yes'
 27        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
 28    END
 29    ELSE
 30    BEGIN
 31                -- The PRINT statement will be ignored and will not cause rule violation if the AllowPrint rule parameter is set to 'yes'
 32        PRINT '  ' + @eventType + ' - ' + @schema;
 33    END
 34
 35    IF @eventType IS NULL
 36    BEGIN
 37        PRINT CONVERT( nvarchar(MAX),
 38                       @data);
 39    END
 40
 41    INSERT INTO [dbo].[_VersionTracker]( [PostTime],
 42                                         [DatabaseUser],
 43                                         [HostName],
 44                                         [Event],
 45                                         [Schema],
 46                                         [Object],
 47                                         [TSQL],
 48                                         [XmlEvent])
 49    OUTPUT inserted.[PostTime],
 50           inserted.[DatabaseUser],
 51           inserted.[HostName],
 52           inserted.[Event],
 53           inserted.[Schema],
 54           inserted.[Object],
 55           inserted.[TSQL],
 56           inserted.[XmlEvent]
 57
 58    VALUES ( GETDATE(),
 59             CONVERT(  sysname,
 60                      SYSTEM_USER),
 61             CONVERT(  sysname,
 62                      HOST_NAME()),
 63             @eventType,
 64             CONVERT(  sysname,
 65                      @schema),
 66             CONVERT(  sysname,
 67                      @object),
 68             @data.value( '(/EVENT_INSTANCE/TSQLCommand)[1]',
 69                          'nvarchar(max)'),
 70             @data
 71           );
 72
 73    -- Result is returned by the select statement
 74    SELECT [PostTime],
 75           [DatabaseUser],
 76           [HostName],
 77           [Event],
 78           [Schema],
 79           [Object],
 80           [TSQL],
 81           [XmlEvent]
 82    FROM [dbo].[_VersionTracker]
 83
 84
 85        INSERT INTO [dbo].[_VersionTracker] 
 86    SELECT TOP 1 [PostTime],
 87           [DatabaseUser],
 88           [HostName],
 89           [Event],
 90           [Schema],
 91           [Object],
 92           [TSQL],
 93           [XmlEvent]
 94    FROM [dbo].[_VersionTracker]
 95
 96    DECLARE @HostName AS nvarchar(255)
 97
 98        -- The SELECT statement is setting a variable value and will not return results
 99    SELECT @HostName=[HostName]
100    FROM [dbo].[_VersionTracker]
101
102
103    INSERT INTO Production.ZeroInventory (ProductID)
104    SELECT ProductID
105    FROM
106    (   MERGE Production.ProductInventory AS pi
107        USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
108               JOIN Sales.SalesOrderHeader AS soh
109               ON sod.SalesOrderID = soh.SalesOrderID
110               AND soh.OrderDate = '20030401'
111               GROUP BY ProductID) AS src (ProductID, OrderQty)
112        ON (pi.ProductID = src.ProductID)
113        WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
114            THEN DELETE
115        WHEN MATCHED
116            THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
117        OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
118    WHERE Action = 'DELETE';
119
120    MERGE Production.ProductInventory AS pi
121    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
122           JOIN Sales.SalesOrderHeader AS soh
123           ON sod.SalesOrderID = soh.SalesOrderID
124           AND soh.OrderDate = '20030401'
125           GROUP BY ProductID) AS src (ProductID, OrderQty)
126    ON (pi.ProductID = src.ProductID)
127    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
128        THEN DELETE
129    WHEN MATCHED
130        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
131    OUTPUT $action, deleted.ProductID;
132
133
134END;

Analysis Results

 MessageLineColumn
1SA0013 : Avoid returning results in triggers.278
2SA0013 : Avoid returning results in triggers.328
3SA0013 : Avoid returning results in triggers.378
4SA0013 : Avoid returning results in triggers.494
5SA0013 : Avoid returning results in triggers.744
6SA0013 : Avoid returning results in triggers.1314
See Also

Other Resources