0

There is a table trigger that was removed in the past that is blocking SSDT publish preview:

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

but in order to remove it from the database it must either be manually dropped (since the publish preview is failing so not even predeploy scripts get run), or the option "drop objects in target not in source" must be enabled.

enter image description here

([trigger] is on a different table than [table]. The table containing [trigger] is having a column removed and [table] is having a column added. Neither column is even referenced by [trigger]!)

We cannot enable this advanced publish setting in general because of other migration scripts that exist in the postdeploy section that rely on deprecated objects, and we cannot have our integration team use this option either because it risks data loss on PROD. But we like to use this option periodically to (manually) test for leftover references to objects that were removed, because the build does not throw errors about them due to deferred name resolution (which presumably exists to enable what I consider a pungent code smell to create non-temporary objects at runtime), as the error can go unnoticed otherwise until a newer system that never had the object introduced is finally tested.

How is this situation fully resolved without pre-model scripting?

Improve this question
3
  • Why not remove the offending object manually?
    – CasualFisher
    Commented Aug 25, 2018 at 21:29
  • Integration really hates doing that sort of thing, so I'd prefer an alternative.
    – Elaskanator
    Commented Aug 27, 2018 at 17:14
  • 1
    I get that, but what do they hate more. Production that’s out of sync with the codebase or manually running a script to bring it back into line? You could even store the script in the SSDT project (and not add it to the build), name the file after a ticket reference and put a comment within the file explaining its existence.
    – CasualFisher
    Commented Aug 28, 2018 at 18:04

1 Answer 1

Reset to default
1

There is a setting on the "drop" tab of the advanced publish settings called "Drop DML Triggers not in source." This is checked by default, and thus triggers should be dropped by the publish process. This is true even if "drop object in target but not in source" is unchecked.

It sounds like this has been unchecked in your publish process. You can confirm that by looking for this element in your publish XML file (if that's how you're publishing):

<DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>

Removing that element, or setting it to true, should resolve your issue without setting the undesirable "drop object in target but not in source" or having to use a pre-model script.

Regarding the inciting issue:

There is a table trigger that was removed in the past that is blocking SSDT publish preview (due to a dependency)...

If the setting I mentioned above doesn't resolve the problem, it would be helpful if you could provide a repro steps for this scenario.

Improve this answer
2
  • A column in the table with the trigger is being dropped, and a column in a table referenced by the trigger is being added. I don't even see why it's complaining about those changes because they don't appear to interfere with each other, because the trigger references neither column! The option to "drop DML triggers not in source" is also enabled.
    – Elaskanator
    Commented Aug 27, 2018 at 17:06
  • 1
    @Elaskanator thanks for the additional details. It would be helpful if you could provide repro steps for this scenario.
    – Josh Darnell
    Commented Aug 27, 2018 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.