SSDT does not consider trigger enable/disable property

Question

I have created an SSDT project from an existing database. It included existing trigger inside the tablename.sql file.

CREATE TABLE [dbo].[TableName] (
    [ID]        INT           NULL
)

GO

CREATE TRIGGER trgTableName ON dbo.TableName
FOR INSERT 
AS
BEGIN
 ....
END

GO

DISABLE TRIGGER [dbo].[trgTableName] ON [dbo].[TableName]; /* My trigger is currently disabled */
GO

However, whenever I modify my trigger, SSDT enables it again. It does not consider Trigger disable property while deployment.

Is there anyway I can get SSDT to disable the trigger (if it is disabled already on the database)?

One of the ways I am thinking is to add as a post deployment script. However, it would be good to use existing SSDT feature for this instead of manually adding a post deployment script.

EDIT: It looks like SQL server automatically enables a trigger if we update them. In my case, there is an update to the trigger so it enables the trigger. However, SSDT does not disable it after the update.

Answer 1

One way is of course to move it to PostDeploy. But I suggest to create a single stored procedure:

CREATE PROCEDURE mysp_disable_triggers
AS
BEGIN
    DISABLE TRIGGER [dbo].[trgTableName] ON [dbo].[TableName];
END;

and call it inside post deploy script:

EXEC mysp_disable_triggers;

This way you have a single point of reference and your code is still validated(checking if references exist and it is a proper SQL code).

---

Second approach to be checked is using ALTER TABLE syntax:

CREATE TABLE [dbo].[TableName](...);
GO

CREATE TRIGGER [dbo].[trgTableName] ...;
GO

ALTER TABLE [dbo].[trgTableName] DISABLE TRIGGER [dbo].[TableName];

EDIT: It does not change the behaviour. So the PostDeploy apporach seems to be feasible option.