Microsoft KB Archive/249032

= BUG: Error Message 8644 Returned When Inserting into a Table with a Trigger =

Article ID: 249032

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q249032



BUG #: 57251 (SQLBUG_70)



SYMPTOMS
The following error message may be returned when you are inserting into a table with an insert trigger:

Server: Msg 8644, Level 16, State 3, Procedure Trigger1, Line 14

[Microsoft][ODBC SQL Server Driver][SQL Server]Internal Query Processor

Error: The plan selected for execution does not support the invoked given execution routine.



CAUSE
The error is due to a cursor declaration within the insert trigger having a subquery referencing the virtual INSERTED table. For example: CREATE TRIGGER Trigger1 ON dbo.TableA FOR INSERT AS DECLARE @var1 INT

DECLARE cursor1 CURSOR FOR SELECT col1 FROM TableB WHERE col1 = (SELECT col2 FROM inserted) ---> Subquery <---

OPEN cursor1 FETCH NEXT FROM cursor1 INTO @var1

CLOSE cursor1 DEALLOCATE cursor1 GO



WORKAROUND
Given the example trigger defined in the "Cause" section of this article, the workaround would be to remove the subquery "(SELECT col2 FROM inserted)" from the WHERE clause of the cursor declaration within the trigger. You can do this by assigning the value returned by the subquery to a local variable (such as @VarA) and use @VarA for the WHERE clause of the cursor declaration. For example: CREATE TRIGGER Trigger1 ON dbo.TableA FOR INSERT AS DECLARE @var1 INT DECLARE @VarA int

SELECT @VarA = SELECT col2 FROM inserted DECLARE cursor1 CURSOR FOR SELECT col1 FROM TableB WHERE col1 = @VarA

OPEN cursor1 FETCH NEXT FROM cursor1 INTO @var1

CLOSE cursor1 DEALLOCATE cursor1 GO



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: Query Plan Trigger 8644 Analyzer T-SQL SQL

Keywords: kbbug kbpending KB249032

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.