Search code examples
visual-studio-2012sql-server-2012-datatools

Trigger is blocking database project publish


I have a Visual Studio 2012 database project, containing my database schema. In a post deployment script, I automatically create a number of triggers responsible for logging changes to the table's data.

Unfortunately, when I subsequently modify the tables and re-publish (in this case, adding an identity specification), I'm now getting this error:

This deployment may encounter errors during execution because changes to [dbo].[BenefitInfoVendor] are blocked by [dbo].[tr_BenefitInfoVendor_Audit]'s dependency in the target database.

The publish then refuses to proceed past the Creating publish preview... step.

If I manually delete the trigger, the publish succeeds. But I don't want to have to remember to manually do this every time I update the schema from here on out (or communicate that to the other developers!).

I've tried dropping all of the triggers in a pre deployment script, but that isn't "soon" enough to stop this error.

Is there any way to suppress this error in Visual Studio, or otherwise let it know that I'm going to take care of the triggers myself?


Solution

  • In a subsequent project, I found a solution that seems to work around this issue. I add a stub to each table script that creates a minimal trigger with the same name as the generated trigger, but which does nothing.

    This is a small amount of additional typing, and allows me to keep my trigger logic factored out.

    CREATE TABLE [dbo].[AlertingDeviceTypes]
    (
        [AlertingDeviceTypeId] INT NOT NULL PRIMARY KEY IDENTITY, 
        [Name] NVARCHAR(MAX) NOT NULL, 
        --Other fields...
        [Deleted] BIT NOT NULL DEFAULT 0, 
        [DeletionId] UNIQUEIDENTIFIER NULL
    )
    GO
    
    CREATE TRIGGER [dbo].[tr_AlertingDeviceTypes_SetDeletionId] ON [dbo].[AlertingDeviceTypes] After UPDATE
        AS
        BEGIN
            --This is a placeholder for the autogenerated trigger.
            noop:
        END
    GO