BUG: Insert Trigger that Issues a Rollback May Cause Error 1203

An INSERT trigger may cause a 1203 error when all of the following conditions are met:

• The trigger issues a rollback.
• There are TSQL statements after the rollback statement.
• The table the trigger is defined on has a foreign key constraint defined.
• The trigger is fired as a result of running an INSERT SELECT.
• The client that issues the INSERT SELECT statement sets implicit_transactions on.

Following is the error message: This error should not affect other connections.

To work around this problem, use any one of the following:

• Have the client not issue set implicit_transactions on.
• Use an INSERT statement instead of an INSERT SELECT statement to populate the table on which the trigger is defined.
• Modify the trigger to perform an explicit transaction using begin tran/commit tran.

Microsoft has confirmed this to be a problem in SQL Server 6.5 Service Pack 5a.