I ran into an issue last week where we renamed a bunch of tables in a SQL Server and it broke a bunch of triggers. I would have though SQL Server Mgmt Studio would have been smart enough to update those triggers but clearly not. Am I expecting too much? Is there a way to detect these issue where a trigger is referencing a table (or column as well) that no longer exists?
Trying the following in SSMS..
CREATE TABLE [dbo].[T] ( X INT ); GO CREATE TRIGGER [dbo].[TR] ON [dbo].[T] FOR INSERT AS SELECT * FROM [dbo].[T]
Then renaming the table as T2 and scripting out the trigger returns the following so I assume you are referring to the second table reference.
CREATE TRIGGER [dbo].[TR] ON [dbo].[T2] FOR INSERT AS SELECT * FROM [dbo].[T]
If you use SQL Server Data Tools to manage your database schema instead of making schema changes directly in SSMS then this does have a rename refactoring. It isn't 100% comprehensive as you would still need to review for any possible usages in dynamic SQL or references to the table in external scripts but it would fix the issue above.