CODEWITHSUNDEEP
sql-servertriggerssql-server-data-toolsdatabase-projectdacpac
Martin Thøgersen
Martin Thøgersen

Reputation: 1649

How to not disable triggers during deployment in SQL Server

When deploying SQL Server Database projects (using either the SSDT Publish wizard or DACPAC deployment), the automated SQL scripts starts by disabling all triggers.

How do I avoid this?

And what are the side effects of not disabling triggers?

PRINT N'Disabling all DDL triggers...'
GO
DISABLE TRIGGER ALL ON DATABASE
GO
<ALL DB CHANGES>
GO
PRINT N'Reenabling DDL triggers...'
GO
ENABLE TRIGGER [LogSchemaChanges] ON DATABASE
GO
PRINT N'Update complete.';

Why would I do this? We have a trigger that logs all schema changes to an audit log. ...and Isn't it ironic, don't you think...

Upvotes: 3

Views: 1521

Answers (1)

Martin Th&#248;gersen
Martin Th&#248;gersen

Reputation: 1649

For manual Publish Wizard deployment, change the behaviour under:

  1. Advanced Publish Settings > General > Advanced Deployment Options.
  2. Deselect the "Disable and reenable DDL triggers".

For automated DACPAC deployment via SQLPackage, set the following publish property:

/p:DisableAndReenableDdlTriggers=False

Specifies whether Data Definition Language (DDL) triggers are disabled at the beginning of the publish process and re-enabled at the end of the publish action.

Upvotes: 4

Related Questions