Microsoft KB Archive/249032

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


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