http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/16/a-month-of-powershell-day-16-databases-triggers/

Printed 2014/07/31 06:05AM

A Month of PowerShell – Day 16 (Databases: Triggers)

By Wayne Sheffield, 2013/02/16

Welcome to Day 16 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://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:

Source code   
#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.

Source code   
#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.

Source code   
#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()
}

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.