5

Have a very strange problem when trying to rename table and use it.
I have a table called oldTable and rename it to the newTable.
I can successfully use select on this table using:

SELECT * FROM database.dbo.newTable;

But when I try to use update like this:

UPDATE database.dbo.newTable SET foo = bar where id = 1;

I receive following error:

Msg 208, Level 16, State 1, Procedure archive, Line 4 [Batch Start Line 0]
Invalid object name 'oldTable'.

Looks like name oldTable was stored somewhere and is used here by some kind of reference. It happens on both ssms and raw php+sql when trying to update.
Anyone have any idea?

Grzesiek
  • 442
  • 4
  • 15
  • 6
    Is there a trigger on the table? – Alex K. Dec 05 '16 at 12:50
  • 5
    Check whether any Update trigger present in `database.dbo.newTable` table. May be the trigger still using the Old table name. We should always read the warnings carefully `Caution: Changing any part of an object name could break scripts and stored procedures` – Pரதீப் Dec 05 '16 at 12:52
  • 1
    Try closing the session and reopen a new one. – FDavidov Dec 05 '16 at 12:53
  • @Alex, Yes there is. And voila... Just updated it and works. This is the solution. Thank you. I do not see option to accept this as anwer, so please post it so I can accept. – Grzesiek Dec 05 '16 at 12:57

1 Answers1

3

Renaming a table will not update any Triggers that have been defined for the table (or any references to it anywhere else) so you need to manually update any triggers or other dependencies to reflect the new name.

Alex K.
  • 171,639
  • 30
  • 264
  • 288