BUG: Misleading Error Message About Ambiguous Table

• Microsoft SQL Server version 6.5

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 a wrong tablename.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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