Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Disable SQL Server Triggers with PowerShell


By:   |   Last Updated: 2016-09-05   |   Comments   |   Related Tips: More > Triggers

Problem

We do remote deployments (or special ETL extracts/loads) to SQL Server databases that have tables with triggers. Sometimes, we want to disable triggers on specific tables, while other times we want to disable every trigger during the deployment or ETL load and until we've completed testing, or we've completed the one time load. Is there a way to automate disabling SQL Server Triggers with PowerShell?

Solution

Yes.  Let's look at some options to disable triggers using SQL Server Management Studio, T-SQL code and PowerShell.

Disable SQL Server Triggers in SQL Server Management Studio

We can disable triggers through the SQL Server Management Studio interface by navigating to the instance | Databases | < Database Name > | Triggers | <Trigger Name> | right click and select the 'Disable' option as shown below:

disable triggers

If we only maintain one server and have one or two triggers, that may be the fastest way to do so, but it isn't something we can automate.

Disable SQL Server Triggers with T-SQL Code

We can also disable SQL Server triggers with T-SQL scripts:

DISABLE TRIGGER dbo.trg_tblTrigger ON dbo.tblTrigger

Disable SQL Server Triggers with PowerShell

We can do this with PowerShell as well. The SMO library (SQL Server Management Objects) comes with the trigger class and if a trigger is on, then its property IsEnabled will be true, according to MSDN, and vice versa if it's off (the property would be false). Using .NET, we can alter a trigger to its opposite state, and in this example we will only disable a trigger that is on by filtering for triggers that are enabled (second function). I also include my Execute-Sql function for saving which triggers were disabled by saving the enable trigger T-SQL script for each of the triggers that are disabled. Below this code block is the post deploy (or ETL) command table for executing commands that need to be completed when ready.

Function Execute-Sql {    Param(
        [Parameter(Mandatory=$true)][string]$server
        , [Parameter(Mandatory=$true)][string]$database
        , [Parameter(Mandatory=$true)][string]$command
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=true"
        
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandTimeout = 30
        $cmd.CommandText = $command

        try
        {
            $scon.Open()
            $cmd.ExecuteNonQuery() | Out-Null
        }
        catch [Exception]
        {
            Write-Warning "Execute-Sql ($server)"
            Write-Warning $_.Exception.Message
            Write-Warning $command
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }
    }
}


Function Disable-Triggers {
    Param(
        [ValidateSet("2008R2","2012","2014","2016")][string]$version
        ,[Parameter(Mandatory=$true)][string]$server
        , [Parameter(Mandatory=$true)][string]$database
        , [Parameter(Mandatory=$false)][string]$table
    )
    Process
    {
        switch ($version)
        {
            "2008R2" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2012" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2014" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2016" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }
        }

        $disablesrv = New-Object Microsoft.SqlServer.Management.Smo.Server($server)
        
        if ($table -eq "")
        {
            foreach ($trgtab in $disablesrv.Databases[$database].Tables)
            {
                foreach ($trigger in $trgtab.Triggers | Where-Object {$_.IsEnabled -eq $true})
                {
                    $output = "INSERT INTO tblPostDeployCmd VALUES ('ENABLE TRIGGER " + $trgtab.Schema + "." + $trigger.Name + " on " + $trgtab.Schema + "." + $trgtab.Name + "')"
                    Execute-Sql -server $server -database $database -command $output
                    $trigger.IsEnabled = $false
                    $trigger.Alter()
                    ### Confirm:
                    $trigger.IsEnabled
                }
            }
        }
        else
        {
            foreach ($trigger in $disablesrv.Databases[$database].Tables[$table].Triggers | Where-Object {$_.IsEnabled -eq $true})
            {
                $output = "INSERT INTO tblPostDeployCmd VALUES ('ENABLE TRIGGER " + $trgtab.Schema + "." + $trigger.Name + " on " + $trgtab.Schema + "." + $trgtab.Name + "')"
                Execute-Sql -server $server -database $database -command $output
                $trigger.IsEnabled = $false
                $trigger.Alter()
                ### Confirm:
                $trigger.IsEnabled
            }
        }
    }
}

Table structure:

---- This table would need to exist before running the above script
CREATE TABLE tblPostDeployCmd(
    PostDeployCmd NVARCHAR(MAX)
)

In rare cases, we may keep off the disabled triggers permanently and we can skip saving any output to enable the triggers. But let's suppose that I want to re-enable only the triggers that I disabled f for a brief period of time. I will add a step borrowing from the T-SQL above this, since the PowerShell script occurs on the server and database level. I could also save this to a table with individual columns and have PowerShell call the information by reading from the saved information in the table, such as running the same script, except enabling specific triggers instead of re-enabling them. However, I can also use PowerShell or a T-SQL code in a SQL Server Agent Job to read from the table of the triggers that were disabled by calling the enabling scripts.

DECLARE @all INT, @cmd NVARCHAR(MAX)SELECT @all = COUNT(PostDeployCmd) FROM tblPostDeployCmd


WHILE @all > 0
BEGIN
 SELECT @cmd = PostDeployCmd FROM tblPostDeployCmd
 EXEC sp_executesql @cmd
 DELETE FROM tblPostDeployCmd WHERE PostDeployCmd = @cmd
 SET @all = @all - 1
END

This is just one of many ways to re-enable triggers that were disabled during a deployment or ETL process.

Next Steps
  • BeBe careful when disabling triggers that may not be a part of the ETL or deployment process. In these cases, call the PowerShell script by a specific table to minimize disabling triggers that don't need to be enabled.
  • At the end of a deployment, a SQL Server Agent Job can call a T-SQL script or a PowerShell script can read from the table where the scripts are stored for re-enabling.  You can use the above simple loop as well.
  • While PowerShell can help in multi-server environments, it's not the only way to disable and enable triggers.
  • Review these related tips:


Last Updated: 2016-09-05


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools