本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章，第2.15节，作者：【加拿大】Donabel Santos，更多章节内容可以访问云栖社区“异步社区”公众号查看。
2.15 创建触发器
本方案描述了如何以编程方式用PowerShell和SMO在SQL Server中创建触发器。
2.15.1 准备
在本方案中，我们将在AdventureWorks2008R2数据库中使用Person.Person表。我们将创建一个AFTER触发器，只显示插入和删除的记录。
下面是等价的完成该任务的T-SQL脚本。
CREATE TRIGGER [Person].[tr_u_Person]
ON [Person].[Person]
AFTER UPDATE
AS
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LastName AS NewLastName,
i.FirstName AS NewFirstName,
d.LastName AS OldLastName,
d.FirstName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.BusinessEntityID = d.BusinessEntityID
2.15.2 如何做…
1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。
2．导入SQLPS模块，创建一个新的SMO服务器对象。
#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName
3．添加如下代码并运行。
$dbName = "AdventureWorks2008R2"
$db = $server.Databases[$dbName]
$tableName = "Person"
$schemaName = "Person"
#get a handle to the Person.Person table
$table = $db.Tables |
Where Schema -Like "$schemaName" |
Where Name -Like "$tableName"
$triggerName = "tr_u_Person";
#note here we need to check triggers attached to table
$trigger = $table.Triggers[$triggerName]
#if trigger exists, drop it
if ($trigger)
{
$trigger.Drop()
}
$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger
-ArgumentList $table, $triggerName
$trigger.TextMode = $false
#this is just an update trigger
$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false
#3 options for ActivationOrder: First, Last, None
$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None
$trigger.ImplementationType =
[Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql
#simple example
$trigger.TextBody = @"
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LastName AS NewLastName,
i.FirstName AS NewFirstName,
d.LastName AS OldLastName,
d.FirstName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.BusinessEntityID = d.BusinessEntityID
"@
$trigger.Create()
4．检查是否触发器已经创建。打开SSMS。
5．使用PowerShell测试触发器。
$firstName = "Frankk"
$result = Invoke-Sqlcmd `
-Query "UPDATE Person.Person SET FirstName = `'$firstName`' WHERE
BusinessEntityID = 2081 " `
-ServerInstance "$instanceName" `
-Database $dbName
$result | Format-Table -AutoSize
类似结果如下：
2.15.3 如何实现…
这个代码相当长，所以我们将在这里拆分它。
为了创建触发器，首先要创建一个实例和数据库的参照。就像我们在本章的大多数方案中所做的一样，假设你跳过了之前的方案。
触发器是绑定到表或视图的。你需要创建一个标量，指向你想要触发的表。
$tableName = "Person"
$schemaName = "Person"
$table = $db.Tables |
Where Schema -Like "$schemaName" |
Where Name -Like "$tableName"
对于本方案，如果触发器已存在，我们将删除它。
$trigger = $table.Triggers[$triggerName]
#if trigger exists, drop it
if ($trigger)
{
$trigger.Drop()
}
接下来，我们需要创建一个SMO trigger对象。
$trigger = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger –ArgumentList
$table, $triggerName
接下来，设置TextMode属性。如果设置为true，意味着你必须自己定义触发器的头部信息。否则，SMO将为你自动生成触发器的头部信息。
$trigger.TextMode = $f
a```
lse
你也需要定义DML触发器的类型：insert、update或delete触发器。我们这里是一个update触发器。
this is just an update trigger
$trigger.Insert = $false
$trigger.Update = $true
$trigger.Delete = $false
你也可以定义触发器顺序。默认情况下，不能保证触发器将会以什么顺序被SQL Server运行，但是可以设置为First或Last。在我们的例子中，我们保留默认值，但仍明确将它定义为可读。
3 options for ActivationOrder: First, Last, None
$trigger.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::None
我们的触发器是T-SQL触发器。SQL Server SMO也支持SQLCLR触发器。
$trigger.ImplementationType =
为了明确触发器的定义，我们将会设置触发器的TextBody属性值。你可以使用字符串为TextBody属性分配触发器的代码块。
simple example
$trigger.TextBody = @"
SELECT
GETDATE() AS UpdatedOn,
SYSTEM_USER AS UpdatedBy,
i.LastName AS NewLastName,
i.FirstName AS NewFirstName,
d.LastName AS OldLastName,
d.FirstName AS OldFirstName
FROM
inserted i
INNER JOIN deleted d
ON i.BusinessEntityID = d.BusinessEntityID
"@
当就绪后，调用触发器的Create()方法。$trigger.Create()
