Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-10
13
Medium Priority
?
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39917698
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
ID: 39917702
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
ID: 39917744
"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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39917746
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
ID: 39917783
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 200 total points
ID: 39917875
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
 
LVL 1

Author Comment

by:midfde
ID: 39917923
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917951
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
ID: 39917965
Yes, I know a bit about refactoring. I've used it in VS a few times. Not often though.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39917986
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
ID: 39918177
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
ID: 39920235
>>"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
ID: 39932362
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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