Trigger is blocking database project publish

Question

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?

Answer 1

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

Answer 2

I had the same problem. I tried various combinations of 'Ignore' properties at least remotely connected to triggers, but without success :(

Next I bypassed it by unchecking 'Verify deployment' property (the last one in the list accessed by Advanced... button in publish databse window).

This did work for me, but it can lead to real problems not getting caught before actual deployment. Since I was already used to manually going over generated script to check for potential problems (such as new not null columns without DF constraints), I don't have to change my routine.

Finally, I used 'Treat verification errors as warnings' property instead. The effect is the same, but you'll actually get list of potential problems in your publish preview