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:
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