I'm trying to rename columns in a table for which there is a trigger. I've used SQL > Refactor > Rename to rename the column. And the trigger file also gets updated. However, when I publish, I get this:

Procedure TR_accrual_Accrual_Update, Line 134 Invalid column name 'MinHoursRule'.

That's the old column name. I'm assuming the publish is updating the table first, and sees that the current/old trigger still has the old column name.

Is it possible to rename a column, update the trigger, and publish?

The only solution I can really think of is to do this:

• Delete the triggers and publish
• Rename the columns
• Add the triggers again
• Publish

CodePudding user response：

This is what I did as a work-around:

1. Add the new columns
2. Leave the old columns
3. Have the trigger use both sets of columns
4. Publish/deploy to prod soon
5. Remove the old columns
6. Publish/deploy to prod later

So, instead of renaming, I just created new columns, and then eventually deleted the old ones.

Yuck. But it worked.

Note: In our C# domain models, I only reference the new columns.

CodePudding user response：

I guess that you have something wrong with the publish profile settings. You might have something disabled, for example "Do not modify triggers" or something like that. I just created new SSDT project in VS 2019 with following structure:

CREATE TABLE [dbo].[test]
(
    [Id] INT ,
    b int
)

GO

CREATE TRIGGER [dbo].[Trigger_test]
    ON [dbo].[test]
    FOR DELETE, INSERT, UPDATE
    AS
    BEGIN
        SET NoCount ON
        insert into test2 select b from inserted
    END
GO
CREATE TABLE [dbo].[test2]
(
    a int
)
GO

Published the project with default settings to the new database and made single insert to the dbo.test table. Made sure that there is record in dbo.test2 table. After that I re-factored dbo.test.b column to dbo.test.a then published again and everything worked. This is generated script:

/*
Deployment script for trg_test

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "trg_test"
:setvar DefaultFilePrefix "trg_test"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'The following operation was generated from a refactoring log file 80d0e5de-e188-465e-b83c-18f38a1cec98';

PRINT N'Rename [dbo].[test].[b] to a';


GO
EXECUTE sp_rename @objname = N'[dbo].[test].[b]', @newname = N'a', @objtype = N'COLUMN';


GO
PRINT N'Altering Trigger [dbo].[Trigger_test]...';


GO

ALTER TRIGGER [dbo].[Trigger_test]
    ON [dbo].[test]
    FOR DELETE, INSERT, UPDATE
    AS
    BEGIN
        SET NoCount ON
        insert into test2 select a from inserted
    END
GO
-- Refactoring step to update target server with deployed transaction logs

IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
    CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '80d0e5de-e188-465e-b83c-18f38a1cec98')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('80d0e5de-e188-465e-b83c-18f38a1cec98')

GO

GO
PRINT N'Update complete.';


GO