4

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?

  • 3
    It's the same for Stored Procedures and views as well. SQL server DOES warm you that it will break anything referencing the old object name – Mark Sinkinson Jun 20 '15 at 11:04
7

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.