Sometimes it’s best not to ask why. However, if for some reason you have a number of triggers on tables within a database that you would like to temporarily disable, read on.
I came across a situation recently while automating a process to refresh test environments where this exact scenario came up. As part of the process several scripts were run to obfuscate production data. While these ran all the UPDATE triggers were firing. Not only were the triggers adding a significant amount of time to the process, they were also updating dates and other values that we’d prefer kept their original values.
Now, as I mentioned this is not a discussion on whether this is a good database design or not, this is just how to solve this issue.
In the snippet below I use Connect-DbaInstance from dbatools to create a $svr object. If you don’t have dbatools installed you could either install dbatools, or use New-Object Microsoft.SqlServer.Management.Smo.Server. The dbatools function is essentially a wrapper around this command that adds a lot of additional checks and options.
I have also defined an array $triggers to keep track of the triggers I disable. It’s likely that you’ll want to put the environment back to how it started, so this will make sure you don’t enable any triggers that started off disabled.
Then we get to the actual work. Using the $svr object we can loop through all the tables, and then all the triggers on those tables. If a certain trigger is enabled, it is added to the $triggers array and then disabled using $tr.isenabled. As with most (all?) changes made through SMO you then need to call the alter method ,$tr.alter(), to actually make the change on the server.
$database = ‘AdventureWorks2017’
$svr = Connect-DbaInstance -SqlInstance server1
$foreach ($tbl in $svr.databases[$database].Tables)
{
foreach ($tr in $($tbl.Triggers | Where-Object Isenabled)) {
$triggers += $tr | Select-Object @{l='SchemaName';e={$tbl.Schema}}, @{l='TableName';e={$tbl.name}}, @{l='TriggerName';e={$_.name}}
$tr.isenabled = $FALSE
$tr.alter()
}
}
When you are ready to enable the triggers again you can use the following code. This loops through the triggers that we had previously disabled and added to our array and enables them.
foreach($tr in $triggers) {
$trigger = $svr.Databases[$database].Tables[$tr.TableName,$tr.SchemaName].Triggers[$tr.TriggerName]
$trigger.IsEnabled = $true
$trigger.alter()
}
One very tiny warning – if any triggers were already disabled when this code first runs, and then you run the second phase (enable all triggers), then you’ve changed the state on the server. May want to add a first step to list out the already-existing, already-disabled triggers before you start – just to see if any are going to surprise you when you enable ’em. (Boy, have I got a set of burn marks from that.)
Hey Brent,
Thanks for the comment! In the first section when I loop through the triggers I’m only including enabled ones (`Where-Object Isenabled`), and saving those into the `$triggers` variable. Then re-enable them based off that variable. Is that what you mean, or am I missing others?
Jess
Hi.
It looks like an elegant solution for this scenario and I like Powershell, but here the question: Why do you perform it by Powershell and not via T-SQL?
I would imagine a simple T-SQL batch from SSMS before going through Powershell, something like the following:
— Select Active Triggers and prepare Disable and Enable T-SQL Statement and put into Temp Table
SELECT ‘ALTER TABLE [‘ + sc.name + ‘].[‘ + ta.name + ‘] DISABLE TRIGGER [‘ + tr.name + ‘];’ AS DisableTriggerStatement,
‘ALTER TABLE [‘ + sc.name + ‘].[‘ + ta.name + ‘] ENABLE TRIGGER [‘ + tr.name + ‘];’ AS EnableTriggerStatement
INTO #Triggers
FROM sys.triggers tr
INNER JOIN sys.tables ta ON ta.object_id = tr.parent_id
INNER JOIN sys.schemas sc ON sc.schema_id = ta.schema_id
WHERE tr.is_disabled = 0;
— Perpare Variable Script for Execution
DECLARE @Disable_Statement nvarchar(max) = (SELECT tmp.DisableTriggerStatement + CHAR(10) AS ‘data()’
FROM #Triggers tmp
FOR XML PATH(”));
— Execute SQL
EXECUTE sys.sp_executesql @Disable_Statement;
— Perpare Variable Script for Execution
DECLARE @Enable_Statement nvarchar(max) = ( SELECT tmp.DisableTriggerStatement + CHAR(10) AS ‘data()’
FROM #Triggers tmp
FOR XML PATH(”));
— Execute SQL
EXECUTE sys.sp_executesql @Enable_Statement;
DROP TABLE #Triggers;
For this scenario the rest of my script was being built in PowerShell, there are for sure many ways to achieve this, including T-SQL. Thanks for reading!
I would add another warning
consider that happens if you accidentally close script window or your server restarts after your disabled triggers and started other tasks…
You come back and don’t have log whatsoever of previously active triggers to re-enable.
You are right there, great point. I should write a follow up perhaps. The approach I used in the past was to export the triggers I disabled to a json file, then read it back in.
Hi,
The powershell approach is interesting, but what about this one? 🙂
DISABLE TRIGGER ALL ON DATABASE;
https://docs.microsoft.com/en-us/sql/t-sql/statements/disable-trigger-transact-sql?view=sql-server-2017
Cris
Another possible solution. I was using this in a scenario where I already had the rest of the work written in PowerShell so it fit in nicely.