Solved

How to change the column name in a table with triggers?

Posted on 2014-03-10
13
314 Views
Last Modified: 2014-03-16
What is the best practice to change a name of a column in a table with triggers by means of SSMS (T-SQL statements might be a bonus) please? I'd like to avoid "Can't alter column name id to pnsid because the name id is used in its trigger" and at the same time "Can't alter this trigger because the name pnsid is not in the table".

'fde.key' table
- Unable to preserve trigger 'u_fde.key'.  
Invalid column name 'pid'.
Invalid column name 'pid'.

Msg 207, Level 16, State 1, Procedure u_fde.key, Line 14
Invalid column name 'pnsid'.

Open in new window

0
Comment
Question by:midfde
13 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
In principle you need to disable the triggers first, make changes to the table and then edit the trigger to correct the change in column name, then finally enable the trigger.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
See here for code on enabling and disabling the triggers on a table.

http://sqltidbits.com/scripts/disable-or-enable-all-triggers-table
0
 
LVL 1

Author Comment

by:midfde
Comment Utility
"When a trigger is disabled, it remains as a database object, but does not execute based on the programmed condition." The aforementioned errors are raised whether the triggers are enabled or not.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
I assume you have copies of the trigger code? If so, drop them, make the changes, then edit the triggers accordingly and reapply them.

If you don't have copies of the trigger code outside of SQL (personally I think you should retain copies of all sp's, functions and triggers outside of SQL), then extract them and then follow my instructions.
0
 
LVL 1

Author Comment

by:midfde
Comment Utility
My question was whether the experts know what the best practice is. (At least better than "script and drop everything and use notepad.exe")
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 100 total points
Comment Utility
See here:
http://technet.microsoft.com/en-us/library/ms188617.aspx


Specifically:
Limitations and Restrictions

Renaming a column will not automatically rename references to that column. You must modify any objects that reference the renamed column manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.



I can't give a best practice on something that, as far as I am aware and from what I have found, you cannot do without scripting out and making the changes manually. The above reference is with regards to SQL 2012, so you can assume the same restrictions on previous versions.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:midfde
Comment Utility
OK, thanks Lee. I'll take this as solution unless... something interesting happens.

You know about "Refactor" operation in MSVS. Don't you?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
my "best practice" is to keep column names as they are, unless a complete application rewrite is done. for some applications I may implement a VIEW (with "better" namings) to hide the actual column names, but I actually never touch the table again, normally.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Yes, I know a bit about refactoring. I've used it in VS a few times. Not often though.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You could use T-SQL to:

1) optionally, put an exclusive lock on the table; this prevents anyone from modifying the table while the triggers are disabled
2) dynamically capture all existing trigger code on that table
3) dynamically drop all triggers
4) use EXEC sp_rename to rename the columns
5) dynamically modify the trigger code -- this could of course have issues if the column name appears in places other than for that single column
6) run code to re-create the trigger(s) with the new column name(s)

No SSMS, no notepad, no manual intervention ... other than the initial EXEC to get the code running :-)
0
 
LVL 1

Assisted Solution

by:midfde
midfde earned 0 total points
Comment Utility
Right. But nearly 20-year old product might somehow, perhaps not perfectly well, "understand" what needs to be done to rename a column (BTW, We are talking about design of a new database process here, so that production data do not yet exist and replication is not enabled.)
PS:
"How to...?" -- "Just don't!" is not acceptable.
0
 
LVL 1

Accepted Solution

by:
midfde earned 0 total points
Comment Utility
>>"You must modify any objects that reference the renamed column manually"
Sure, but the following although does not cure the problem completely, helps nevertheless significanly.
exec sp_rename  '[fde.key].id', 'pnsid' , 'column'

Open in new window

It also somewhat invalidates the quoted assertion.
0
 
LVL 1

Author Closing Comment

by:midfde
Comment Utility
Oh, you (I am talking about myself, sorry) know how to use T-SQL? Think again.
Besides, although sp_rename is (so old -- shame on me! -- and) good, Refactor would have been better.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now