Troubleshooting problems with SQL Server 2014 Triggers
If you have problems with SQL Server 2014 triggers, review this troubleshooting checklist to
find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many SQL Server 2014 triggers bugs were fixed in SQL Server service packs, you should
install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. The error 217 may occur when a trigger runs.
This error indicates that maximum trigger nesting level exceeded. The triggers can be nested up
to 32 levels. In this case, when the nesting level is exceeded the trigger terminates. If you
receive error 217, you need to rewrite the triggers code.
3. An AFTER INSERT trigger may insert only one row instead of multiple rows, if you have a
SSIS package that inserts data into a table using “Table or View” direct access mode.
This problem occurs because SSIS incorrectly calls “SET ROWCOUNT 1” on the session that is
executing the INSERT AFTER trigger. To work around this problem, use “Table or view – fast load”
option in the OLE DB Destination editor and in the Advanced Editor dialog box, go to the
Component Properties tab and in the FastLoadOptions add the value “FIRE_TRIGGERS”.
4. You can get the error 531 during trigger execution.
This error occurs because you cannot set NOCOUNT to OFF inside the trigger execution when the
server option “disallow_results_from_triggers” is true. To work around this problem, you can
set the server option “disallow_results_from_triggers” to false before setting NOCOUNT to OFF
inside the trigger.
5. Use TRY-CATCH logic to trap and handle error conditions in triggers.
SQL Server 2014 has the TRY…CATCH construction, which allows you to make exception handling
like in the C# and C++ languages. So, you can prevent SQL Server 2014 from raising error
message and you can suppress the error using the TRY…CATCH block.
6. The error 570 may occur during trigger execution.
This error indicates that INSTEAD OF triggers do not support direct recursion. When the
error 570 occurs, the trigger execution failed. To resolve this problem, avoid direct recursion
inside the INSTEAD OF triggers.
7. You can get the error 575 when a LOGON trigger runs.
This error occurs when a LOGON trigger returned a resultset. To resolve this problem, you should
modify the LOGON trigger to not return resultsets.
8. You can get the error 1034.
This error indicates that duplicate specification of the action in the trigger declaration
occurs. To resolve this problem, you should rewrite the trigger’s code to avoid duplicate
specifications.
9. The error 3616 may occur during trigger execution.
In this case, the batch will be aborted and the user transaction, if any, will be rolled back.
To resolve this problem, you need to rewrite the triggers code.
10. The error message may occur when you use the replication feature to run an
INSERT statement in a trigger.
This is the error message text: “Explicit value must be specified for identity column”.
This problem occurs when the “NOT FOR REPLICATION” option is enabled for the IDENTITY column.
To work around this problem, you should disable the “NOT FOR REPLICATION” option for the
IDENTITY column.
11. Access violation may occur when the query in the trigger joins the inserted or
deleted table with other tables and runs in parallel.
This problem occurs when you run a query to delete or insert a large set of data in a
database. This bug was first fixed in Cumulative Update package 1 for SQL Server 2014.
You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
12. The error occurs when you execute update query on a table that has a DML trigger.
This is the error message text: “A severe error occurred on the current command. The results,
if any, should be discarded.” This problem occurs when in that DML trigger you join
inserted/deleted table with other tables. This bug was first fixed in Cumulative Update
package 2 for SQL Server 2014. You can download the Cumulative Update package 2 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
To work around this problem, avoid using the inserted/deleted table directly in a query
joining it with other tables, instead cache it in a temp table and use the temp table
in the query.
13. To resolve the triggers performance problems, check the following list:
– Try to minimize the number of tables referenced in a trigger.
– Try to minimize the number of rows affected in a trigger.
– Try to minimize the DML trigger’s code size.
– Try to avoid roll back in the DML triggers.
– Use UPDATE() or COLUMNS_UPDATED() functions inside a trigger.
– Try to use CHECK constraints instead of triggers whenever possible.
– Try to use foreign key constraints instead of triggers to enforce referential integrity.