BUG: Rename Column Causes Error 207 After Trigger Re-resolution (214592)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q214592
BUG #: 18547 (SQLBUG_65)

SYMPTOMS

If a column on a table is renamed and there is a trigger on the table, you may receive the following error after re-resolution of the trigger:
Msg 207, Level 16, State 2 Invalid column name '<old_column_name>'.

WORKAROUND

To work around this problem, drop and re-create triggers after a column name change.

STATUS

Microsoft has confirmed that this is a problem in SQL Server.

MORE INFORMATION

Re-resolution is the process of updating the existing query tree and invalidating any query plans in the cache. Re-resolution occurs when one of the tables changes in such a way that the query tree stored in sysprocedures might be invalid (for example, if the datatypes, column offsets, object IDs, or other parts of the table have changed).

Re-resolution can be triggered by various activities, most of which are controlled by SQL Server, not the procedure owner or the system administrator (SA). Procedure re-resolution occurs after any of the following:

  • Executing a LOAD DATABASE statement on the database containing the procedure.
  • Executing a LOAD DATABASE statement on a database containing a referenced table.
  • Dropping and re-creating a table referenced by the procedure.
  • Dropping and re-creating a database containing a table referenced by the procedure.
  • Binding or unbinding a rule to a table referenced by a query in the procedure.
  • Binding or unbinding a default to a table referenced by a query in the procedure.
During re-resolution, SQL Server rebuilds some parts of the query tree but does not deallocate others.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB214592
©2003 Microsoft Corporation. All rights reserved.