By: Tim Smith | Updated: 2016-09-05 | Comments (1) | Related: > 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:
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:
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-09-05
