Troubleshooting SQL Server 2008 Triggers
If you have problems with SQL Server 2008 triggers, review this troubleshooting checklist to
find potential solutions.
1. Check that you use the latest SQL Server service pack.
Because many SQL Server 2008 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 2008 service pack was service pack 1.
You can download the SQL Server 2008 service pack 1 at here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en
2. The change is not replicated to the subscriber database when you run
the ALTER TRIGGER statement for a trigger in a merge publication.
This problem occurs because the schema name is missing in the sp_MSmerge_altertrigger stored
procedure. This bug was first fixed in Cumulative update package 3 for SQL Server 2008.
You can download the Cumulative update package 3 for SQL Server 2008 at here:
http://support.microsoft.com/kb/960484/LN/
3. The error message occurs when you run a DML statement in a LINQ to SQL
application in SQL Server 2008.
This is the error message text: "The target table 'Table Name' of the DML statement cannot have
any enabled triggers if the statement contains an OUTPUT clause without INTO clause." This is
SQL Server 2008 bug. To resolve this problem, install the latest SQL Server service pack.
4. The UPDATE statement takes a long time to finish when executed against a table
that has a FOR UPDATE trigger that joins the DELETED and INSERTED tables.
This is SQL Server 2008 bug. This bug was first fixed in Cumulative update package 4 for
SQL Server 2008. You can download the Cumulative update package 4 for SQL Server 2008 at here:
http://support.microsoft.com/kb/963036/LN/
5. You can find out that the transfer operation fails when you transfer data for a table
that contains triggers by using SMO or the Copy Data Wizard in SQL Server 2008.
This is SQL Server 2008 bug. This bug was fixed in SQL Server 2008 service pack 1. To resolve this
problem, install the latest SQL Server service pack.
6. You may receive the error message "Non-yielding Scheduler" when you run a
query that uses a join condition and fires a trigger.
This problem occurs when the query uses the Nested Loops join. To work around this problem,
you can use the MERGE or HASH join hints. To resolve this problem, install the latest
SQL Server 2008 service pack.
7. A memory leak occurs for the first trigger object if two triggers are fired in
an ALTER TABLE SWITCH PARTITION statement.
This is SQL Server 2008 bug. This bug was fixed in SQL Server 2008 service pack 1. To resolve this
problem, install the latest SQL Server service pack.
8. The error message occurs when you run an UPDATE statement against a table
that has a DML trigger.
This is the error message text: "An inconsistency was detected during an internal operation".
This is SQL Server 2008 bug. This bug was first fixed in Cumulative update package 1 for
SQL Server 2008. You can download the Cumulative update package 1 for SQL Server 2008 at here:
http://support.microsoft.com/kb/956717/
9. 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.
10. Use TRY-CATCH logic to trap and handle error conditions in triggers.
SQL Server 2008 has the TRY...CATCH construction, which allows you to make exception handling
like in the C# and C++ languages. Now, you can prevent SQL Server 2008 from raising error
message and you can suppress the error using the TRY...CATCH block.
11. 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.
