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'
I tried this, using SQL Server 2012: CREATE TABLE dbo.table_without_meaningful_name(id int identity(1,1) PRIMARY KEY, c1 nvarchar(max),c2 nvarchar(max)) GO CREATE TABLE dbo.log_for_table_without_meaningful_name(id int, c1 nvarchar(max), c2 nvarchar(max), dt datetime default current_timestamp) go create TRIGGER tr_without_meaningful_name ON dbo.table_without_meaningful_name FOR UPDATE,INSERT AS BEGIN set nocount on insert dbo.log_for_table_without_meaningful_name(id,c1,c2) select id,c1,c2 from inserted; END GO insert dbo.table_without_meaningful_name(c1,c2) values('123456789012345678901234567890','this will work'); GO EXECUTE sp_rename N'dbo.table_without_meaningful_name.c2', N'column2', 'COLUMN' This statement works and it does rename the column **c2** to **column2**. I don't get an error message thrown from sp_rename. Neither do I get an error message when changing the column name from the table designer in SSMS. So I can't really reproduce your error message. Can you post some sample code from a trigger, and tell us which version of SQL Server you're using? However, in my example, the actual trigger will break, because it **explicitly** accesses the column **c2**, which I have renamed to **column2**. Solving this automagically will require you to have a DDL-trigger altering the DML-trigger for you. It can be done, but I haven't looked into **how** it can be done. In a DDL-trigger, you aquire the metadata about the change from EVENTDATA(), can store that into an XML-variable and then query the variable to find out which table and which columns were changed.