Welcome to Day 16 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
The last several days we’ve been dealing with tables. Today, we’re going to end up this mini-series of tables by talking about triggers. Today will also start the next mini-series of working with objects that are compiled T-SQL code.
Creating a Trigger
To create a trigger that fires on an update or delete against a table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" $SchemaName = "Common" $TableName = "TestTable" $ObjectName = "trgTestTable" #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" # get the server $Server = New-Object ("$SMO.Server") "$Instance" # assign the database name to a variable $MyDB = $Server.Databases[$DBName] # assign the schema to a variable $Schema = $MyDB.Schemas[$SchemaName] # assign the table name to a variable $Table = $MyDB.Tables.Item($TableName, $SchemaName) # assign the trigger name to a variable $Object = $Table.Triggers.Item($ObjectName) IF (!($Object)) #Check to see if the trigger exists { # it doesn't exist, so create it $Object = New-Object ("$SMO.Trigger") ($Table, $ObjectName) $Object.TextMode = $false $Object.Insert = $false $Object.Update = $true $Object.Delete = $true $Object.TextBody = @' BEGIN ROLLBACK TRANSACTION END '@ $Object.Create() } |
Yeah, that’s right… just rollback any updates or deletes against a table. Not a very useful trigger… but it does show you how to make one in PowerShell.
Altering a trigger
Let’s update this trigger so that it only fires if updating or deleting more than five records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" $SchemaName = "Common" $TableName = "TestTable" $ObjectName = "trgTestTable" #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" # get the server $Server = New-Object ("$SMO.Server") "$Instance" # assign the database name to a variable $MyDB = $Server.Databases[$DBName] # assign the schema to a variable $Schema = $MyDB.Schemas[$SchemaName] # assign the table name to a variable $Table = $MyDB.Tables.Item($TableName, $SchemaName) # assign the trigger name to a variable $Object = $Table.Triggers.Item($ObjectName) IF (($Object)) #Check to see if the trigger exists { # it exists, so it can be changed $Object.TextBody = @' BEGIN IF (SELECT COUNT(*) FROM DELETED) > 5 ROLLBACK TRANSACTION END '@ $Object.Alter() } |
Dropping a trigger
And since this really is a completely useless trigger to have in the database (but it does work out pretty good for showing how to work with triggers in PowerShell), let’s drop it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" $SchemaName = "Common" $TableName = "TestTable" $ObjectName = "trgTestTable" #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" # get the server $Server = New-Object ("$SMO.Server") "$Instance" # assign the database name to a variable $MyDB = $Server.Databases[$DBName] # assign the schema to a variable $Schema = $MyDB.Schemas[$SchemaName] # assign the table name to a variable $Table = $MyDB.Tables.Item($TableName, $SchemaName) # assign the trigger name to a variable $Object = $Table.Triggers.Item($ObjectName) IF (($Object)) #Check to see if the trigger exists { # it exists, so drop it $Object.Drop() } |
