BUG: Error Message 8644 Returned When Inserting into a Table with a Trigger (249032)
The information in this article applies to:
- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (all editions)
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
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
| Modification Type: | Major | Last Reviewed: | 10/17/2003 |
|---|
| Keywords: | kbBug kbpending KB249032 |
|---|
|