使用DACPAC，当表上有一个触发器时，如何重命名列?

Question

我试图重命名有触发器的表中的列。我用过SQL>重构的在Rename用于重命名列。触发器文件也得到更新。然而，当我发布时，我得到这个:

Procedure TR_accrual_Accrual_Update, Line 134无效的列名"MinHoursRule"。

这是旧的列名。我假设发布首先更新表，并看到当前/旧触发器仍然具有旧的列名。

是否可以重命名列、更新触发器并发布?

我能想到的唯一解决办法就是这样做:

• 删除触发器并发布
• 重命名列
• 再次添加触发器
• 发布

Answer 1

我是这样做的:

1. 添加新列
2. 保留旧列
3. 让触发器使用两组列
4. 尽快发布/部署到产品
5. 删除旧列
6. 以后发布/部署到prod

所以，我只是创建了新的列，然后最终删除了旧的列，而不是重命名。

恶心。

注意:在我们的c#领域模型中，我只引用新的列。

Answer 2

我猜你的发布配置文件设置有问题。您可能禁用了某些内容，例如"不修改触发器";或者类似的东西。我刚刚在VS 2019中创建了新的SSDT项目，结构如下:

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

将项目以默认设置发布到新数据库，并对dbo.test表进行单个插入。确保dbo.test2表中有记录。之后，我将dbo.test.b专栏重构为dbo.test.a，然后再次发布，一切正常。这是生成的脚本:

/*
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