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.
