SSDT Publish Window: What does checkbox ENABLE mean

Based on my first-hand experience, SSDT refreshes table definitions before it refreshes triggers.

I had to manually disable all triggers before publishing for it to work (sample reasoning below). However, it is foolish to assume all table triggers were enabled before, so turning them all off then back on is prone to causing issues.

My solution was to create a script to generate the necessary SQL to turn off all currently-enabled triggers, and then to enable them again (make sure to set current database connection to your target database).

Warning: Single user mode will close all open connections. Make sure to re-enable multi-user mode after you're done. You can skip changing user mode altogether if nobody is holding any locks on your system.

SET NOCOUNT ON
DECLARE @DatabaseName NVARCHAR(255) = 'YOURDATABASE'
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
DECLARE @TableTriggersOnly BIT = 1
CREATE TABLE #Trigger
(
     SchemaName NVARCHAR(255)
    ,TableName NVARCHAR(255)
    ,TriggerName NVARCHAR(255)
    ,WasDisabled BIT
)
INSERT INTO #Trigger
(
     SchemaName
    ,TableName
    ,TriggerName
    ,WasDisabled
)
SELECT
     SchemaName = S.name
    ,TableName = T.name
    ,TriggerName = Tr.name
    ,WasDisabled = Tr.is_disabled
FROM
    sys.triggers AS Tr
    INNER JOIN sys.tables AS T ON
        T.object_id = Tr.parent_id
    INNER JOIN sys.schemas AS S ON
        S.schema_id = T.schema_id
WHERE
    Tr.is_disabled = 0
    AND (@TableTriggersOnly = 0 OR Tr.parent_class = 1)
DECLARE
     @SchemaName NVARCHAR(255)
    ,@TableName NVARCHAR(255)
    ,@TriggerName NVARCHAR(255)
    ,@WasDisabled BIT
DECLARE TriggerCursor CURSOR STATIC FOR
    SELECT
         SchemaName
        ,TableName
        ,TriggerName
        ,WasDisabled
    FROM #Trigger
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'DISABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
/*
 * YOUR STATEMENTS HERE
 */
PRINT '/*******************'
PRINT 'YOUR STATEMENTS HERE'
PRINT '*******************/'
/*
 * YOUR STATEMENTS HERE
 */
OPEN TriggerCursor
FETCH NEXT FROM TriggerCursor INTO
     @SchemaName
    ,@TableName
    ,@TriggerName
    ,@WasDisabled
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'ENABLE TRIGGER ' + @SchemaName+'.'+@TriggerName + ' ON ' + @SchemaName+'.'+@TableName + ';'
    FETCH NEXT FROM TriggerCursor INTO
         @SchemaName
        ,@TableName
        ,@TriggerName
        ,@WasDisabled
END
CLOSE TriggerCursor
DEALLOCATE TriggerCursor
PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
DROP TABLE #Trigger

A particular table of mine has a trigger that was poorly written, encountering errors when handling multiple affected rows at a time, but the correction to the trigger to handle the case was never getting applied. So a column alteration in the table meant that SSDT was shifting data to a temp table then back, causing a multi-row invocation of the table trigger.

In our processes using Database projects and DacPacs, we decided against using DacPacs to deploy anything except the basic components that are changed as part of ongoing maintenance and support. This is just tables, views, stored procedures, extended properties, user defined data and table types, Scalar and table Valued Functions.

We instead manage security and database settings via a collaborative approach including the development teams and the database team.

Of course we are not in a situation where we are creating databases from scratch very often and security is pretty stable requiring infrequent changes.But the security is included in the project. To fix the problems we originally had with the logins causing build failures, we changed the Create User objects to eliminate the password:

Create User [user] Without Login;

We have not had problems with drift being detected on these user objects. You could try this aproach to see if it fixes your problem.