Solved

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

Posted on 2014-03-10
13
359 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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 100 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 69

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

737 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