Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

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
midfde
Asked:
midfde
3 Solutions
 
Lee SavidgeCommented:
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
 
Lee SavidgeCommented:
See here for code on enabling and disabling the triggers on a table.

http://sqltidbits.com/scripts/disable-or-enable-all-triggers-table
0
 
midfdeAuthor Commented:
"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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Lee SavidgeCommented:
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
 
midfdeAuthor Commented:
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
 
Lee SavidgeCommented:
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
 
midfdeAuthor Commented:
OK, thanks Lee. I'll take this as solution unless... something interesting happens.

You know about "Refactor" operation in MSVS. Don't you?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Lee SavidgeCommented:
Yes, I know a bit about refactoring. I've used it in VS a few times. Not often though.
0
 
Scott PletcherSenior DBACommented:
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
 
midfdeAuthor Commented:
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
 
midfdeAuthor Commented:
>>"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
 
midfdeAuthor Commented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now