本节书摘来自异步社区出版社《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()