BUG: The error message provides an incorrect table name when you use an ambiguous update statement in SQL Server (153996)


The information in this article applies to:

  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q153996
BUG#: 15305 (6.5)
      57614 (7.0)
BUG#: 224351 (SHILOH)

SYMPTOMS

Inside a trigger, if there is an ambiguous update statement and the first table in the FROM section is the logical table INSERTED or DELETED, the resulting message 8154 has an incorrect tablename. In SQL Server 7.0, the resulting message 1032 has an incorrect tablename.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

The tablename in the message should actually be the table in the UPDATE section. Instead the message shows the truncated name of a different table (the one on which the trigger is being created). Since this does not happen on SQL Server versions 4.21a or 6.0, upgrades of such triggers to SQL Server 6.5 will obviously fail. The following script illustrates the problem: 
   drop table arbitraryname, table2
   go
   create table arbitraryname(x int)
   create table table2(x int)
   go
   create trigger trg1 on arbitraryname for update as
   begin
   update table2 set child.x = parent.x
   from inserted, table2 child, table2 parent
   where inserted.x = child.x
   end
   go
Modification Type:MinorLast Reviewed:3/14/2005
Keywords:kbBug KB153996
©2004 Microsoft Corporation. All rights reserved.