《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.15 创建触发器...

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