Using Powershell to check if Triggers are enabled

I’ve been thinking about how Powershell can be used by the DBA as an extra tool in their armoury. An article that caught my eye was The Daily DBA Checklist, specifically an item about Triggers;

“…last week some idiot turned a host of triggers off in our ERP system, causing a cascade of posting problems on dozens of orders before we caught the root cause…”

SQL Server does contain a system view called sys.triggers to provide us with some detailed information on triggers in our databases. Lets view all triggers in the AdventureWorks database;

SELECT *
FROM sys.triggers;

View a list of disabled triggers;

SELECT name
FROM sys.triggers
WHERE is_disabled = 1;

View a summary of enabled and disabled triggers;

SELECT CASE(is_disabled)
              WHEN 1 THEN 'Disabled'
              WHEN 0 THEN 'Enabled'
          END AS [state],
          COUNT(*) AS [count]
FROM sys.triggers
GROUP BY is_disabled;

Many people may be happy with these methods but here’s a Powershell method that I think is much better;

# Load SMO Extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Set sql server & db info
$sqlserver = "RHYS-PC\sql2005";
$database = "AdventureWorks";

# Create sql server and db objects
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
$db = $srv.Databases[$database];

$triggerCount = 0;
$disabledCount = 0;

# Any Database Triggers?
$DatabaseTriggerCount = $db.Triggers.Count;

if($DatabaseTriggerCount -gt 0)
{
	$triggerCount += $DatabaseTriggerCount;
	foreach($DatabaseTrigger in $db.Triggers)
	{
		$active = $DatabaseTrigger.IsEnabled;
		if($active)
		{
			# Set text to green
			Write-Host -ForegroundColor Green "$DatabaseTrigger IsEnabled = $active.";
		}
		else
		{
			Write-Host -ForegroundColor Red "$DatabaseTrigger IsEnabled = $active.";
			# Increment $disabledCount
			$disabledCount += 1;
		}
	}
}

# For each table
foreach($table in $db.Tables)
{
	# tally up trigger count
	$triggerCount += $table.Triggers.Count;
	# For each trigger on the current table
	foreach($trigger in $table.Triggers)
	{
		$active = $trigger.IsEnabled;
		if($active)
		{
			# Set text to green
			Write-Host -ForegroundColor Green "$trigger IsEnabled = $active.";
		}
		else
		{
			Write-Host -ForegroundColor Red "$trigger IsEnabled = $active.";
			# Increment $disabledCount
			$disabledCount += 1;
		}
	}
}

# Write summary
Write-Host -ForegroundColor Green "=======================================";
Write-Host -ForegroundColor Green "Total triggers in $sqlserver\$database = $triggerCount";
$colour = "Green";
if($disabledCount -gt 0)
{
	# If any triggers are disabled change warning text to red!
	$colour = "Red";
}
Write-Host -ForegroundColor $colour "Total disabled triggers = $disabledCount.";

In this Powershell script I have used coloured text to make certain things stand out. The Powershell method would be easy to adapt to use with multiple servers or take corrective action, i.e. enabling triggers. A Powershell script would be an excellent choice for that final go-live sanity check. I’m going to focus on more items in The Daily DBA Checklist, and similar articles, for further Powershell articles.