《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 

 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``` 

你也需要定义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()

异步社区 异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区，也是国内领先的IT专业图书社区，致力于优质学习内容的出版和分享，实现了纸书电子书的同步上架，于2015年8月上线运营。公众号【异步图书】，每日赠送异步新书。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载