SQL Server

Troubleshooting problems with SQL Server 2016 triggers

Troubleshooting problems with SQL Server 2016 triggers

If you have problems with SQL Server 2016 triggers, review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2016 service pack.

Because many SQL Server 2016 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 2016 service pack was service pack 1. You can
download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. Data loss or incorrect results occur when you try to configure the execution
order of triggers on memory-optimized tables by using the sp_settriggerorder
stored procedure.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 1 for
SQL Server 2016. You can download the Cumulative Update package 1 for SQL Server 2016
at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

3. AFTER DELETE triggers occur in the wrong order in the ON DELETE CASCADE action
chain in SQL Server 2016.

This problem occurs when you create a cascading chain by using the FOREIGN KEY
constraint together with the ON DELETE CASCADE action. This bug was fixed in
Cumulative update package 5 for SQL Server 2016. You can download the Cumulative
Update package 5 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4013105/cumulative-update-5-for-sql-server-2016

4. 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.

5. 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”.

6. 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.

7. Use TRY-CATCH logic to trap and handle error conditions in triggers.

SQL Server 2016 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 2016 from raising error message and you can suppress the error
using the TRY…CATCH block.

8. 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.

9. 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.

10. 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.

11. 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.

12. 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.

13. 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. 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.

14. 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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail