How can I disable All DML Triggers for a database in SQL Server?

Question

I have a database that handles nearly a 100 DML triggers and for some error fixing and maintenance purposes we need to disable all these triggers temporarily.

Is there any way to disable all DML triggers for a single database at once?

I have gone through a lot of articles and they all have suggested either the script for disabling DDL triggers or say to disable one by one which is not a great option in this case.

Any help would be really appreciated. Thank you in advance!

Answer 1

For anyone that still needs this, here's a solution I made for enabling/disabling all DML triggers within a database on SQL Server:

USE MyDatabase;

BEGIN TRY

    DECLARE @NewLine CHAR(2) = CHAR(13) + CHAR(10)

    DECLARE @DynamicSQL VARCHAR(MAX) = (
        SELECT 
            STRING_AGG(
                CONVERT(
                    VARCHAR(MAX), 
                    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(TRO.schema_id)) + '.' + QUOTENAME(TA.name) + ' DISABLE TRIGGER ALL;' + @NewLine),
                '') 
                WITHIN GROUP (ORDER BY TR.name)
        FROM 
            sys.triggers AS TR
            INNER JOIN sys.objects AS TRO ON TR.object_id = TRO.object_id
            INNER JOIN sys.objects AS TA ON TRO.parent_object_id = TA.object_id
        WHERE
            TA.type = 'U') -- U: User defined table

    BEGIN TRANSACTION

        EXEC (@DynamicSQL)
        --SELECT @DynamicSQL AS [processing-instruction(x)] FOR XML PATH('') -- 'Print' longer than 8k characters

    COMMIT

END TRY

BEGIN CATCH
    
    DECLARE @ErrorMessage VARCHAR(MAX) = ERROR_MESSAGE()

    IF @@TRANCOUNT > 0
        ROLLBACK

    RAISERROR(@ErrorMessage, 16, 1)

END CATCH

The use of the STRING_AGG makes this solution only work with SQL Server 2017+, but there are many alternatives to do the same on previous versions.

To enable all triggers, just replace the DISABLE with ENABLE.

Note that DISABLE TRIGGER ALL ON DATABASE does not disable DML triggers on the tables, only DDL triggers created on a database level.