SSDT does not consider trigger enable/disable property

5
votes

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.

2
sql-serversql-server-data-tools
Just mentioning, after importing a database, we move trigger definitions to their own files. This makes them slightly easier to manage. Don't have any disabled ones, so can't help with this question. - Philip Kelley
No problem, I think I will do the same - it is much cleaner approach. - developer
I've lost count of the number of subtle issues within SSDT that resulted in an unexpected outcome. I love the idea of desired state deployments but in the end edge cases like this lead to us dropping SSDT. - David Rushton
Could you check the state of deployment property: Ignore DML trigger state? - Piotr
@DavidRushton You might be using the wrong forums for raising issue with SSDT. You should try raising those issues using Visual Studio into their developer community to get faster response. - Dhruva N

2 Answers

3
votes

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.

1
votes

If you have a big hard-to-manage database and there's lots of this kind of stuff, you might get tricky and create a procedure that scripts out a procedure that captures the state of your triggers and restores them. You can run the procedure at PreDeploy and then run the generated procedure at PostDeploy. Something like this:

create procedure dbo.scriptResetDisableTriggers as 
begin
    set nocount on
    declare 
        @sql nvarchar( max ),
        @n nvarchar( 2 ) = nchar(0x0d) + nchar(0x0a)

    select @sql =  
        N'create procedure dbo.resetDisabledTriggers as' + @n +
        N'begin' + @n +
        N'  set nocount on' + @n + @n;

    select @sql += 
        N'  begin try' + @n +
        N'    disable trigger ' +  tr.name + N' on table ' + s.name + N'.' +    t.name + @n +
        N'  end try' + @n +
        N'  begin catch' + @n +
        N'    print( error_message( ) ) -- or something else cool' + @n +
        N'  end catch' + @n + @n
    from 
        sys.triggers tr
        inner join
        sys.tables t
        on
            tr.parent_id = t.object_id
        inner join
        sys.schemas s
        on
            t.schema_id = s.schema_id
    where
        tr.is_disabled = 1

    select @sql +=  
        @n + N'  drop procedure dbo.resetDisabledTriggers' + @n +
        @n + N'end' + @n

    print ( @sql )
    execute sp_executesql  @sql

end