0

During ssdt deployment I am concerned about having DML triggers in place on tables that may slow down performance of table recreation a lot.

We are facing the issue that the column order in the table scripts is different than in the target servers. Because Microsoft does not provide an option to ignore this, the tables are recreated during publishing to ensure the same column order.

This process is very very slow. And since some tables do have DML triggers enabled, I am concerned this makes it even more slow than required. I looked for options to disable / ignore those triggers during deployment but could not find any. How are we expected to realize that?

  • Why are you suspecting that DML triggers are slowing you down? – spaghettidba Feb 25 '16 at 11:07
  • 1
    Because the whole table is getting re-created and I guess records are put into a temp table and re-inserted to the new table. This should fiire any insert triggers, shoudn't it? – Magier Feb 25 '16 at 11:14
  • Any further ideas? – Magier Aug 9 '16 at 14:46
  • You might consider using a pre and post deploy scripting to manually search for and disable any triggers. My answer below gets you halfway there. You'll want to populate a temporary table with a list of the disabled triggers from the predeploy script then re-enable them in a postdeploy script (making sure to not leave leftover records that would mess up subsequent publishes. – Elaskanator Aug 8 '18 at 21:08
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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