sql-server - SQL Server 中如何正确获取触发器文本？

Question

给定:

CREATE TABLE X (Id INT)
GO
CREATE TRIGGER Y ON X FOR DELETE AS SET NOCOUNT ON;
GO
sp_rename 'X', 'Z'
GO
SELECT sm.definition v1, OBJECT_DEFINITION(t.object_id) v2, c.text v3
FROM sys.triggers t
JOIN sys.sql_modules sm ON t.object_id = sm.object_id AND t.Name = 'Y'
JOIN sys.syscomments c ON t.object_id = c.id
GO
sp_helptext 'Y'

这个简单的查询创建一个表，在上面放置一个触发器，重命名该表并使用四种不同的方式来获取触发器的文本:

1. sys.sql_modules 查看
2. sys.syscomments 查看
3. OBJECT_DEFINITION 函数
4. sp_helptext 函数

所有四种方式都为触发器生成相同的文本:

CREATE TRIGGER Y ON X FOR DELETE AS SET NOCOUNT ON;

当然，这是我的问题，因为此文本不能用于创建触发器 - 表 X 不再存在。

现在，如果我在 SSMS 中选择触发器(当然是在表 Z 上)并编写 CREATE 操作脚本，这就是我得到的:

USE [mydb]
GO

/****** Object:  Trigger [dbo].[Y]    Script Date: 12/22/2017 10:32:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Y] ON [dbo].[Z] FOR DELETE AS SET NOCOUNT ON;
GO

ALTER TABLE [dbo].[Z] ENABLE TRIGGER [Y]
GO

这是正确的，与前面的 4 种方式不同。

所以，我的问题是 - 如何获得正确的触发文本？我们可以运行的那个来重新创建触发器。就像 SSMS 一样。

Answer 1

SSMS 使用 SMO 编写脚本，它更改 CREATE TRIGGER 的 ON 子句以匹配父表的子句。您可以使用如下例所示的 SMO PowerShell 脚本来获取与 SSMS 生成的文本相同的文本。只需为您想要的生成选项调整 PS 脚本。

触发器文本显然不同步，因为在没有重新创建触发器的情况下重命名了表。

try {

    Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll";

    $sqlServerName = "YourServer";
    $databaseName = "YourDatabase";

    $scriptPath = "c:\temp\Z_TriggerScripts.sql";

    $SMOserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName);
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver);

    $database = $SMOserver.databases[$databaseName]

    $objectsToScript = @();
    $objectsToScript += $database.Tables["Z"].Triggers;

    $scriptr.Options.FileName = $scriptPath;
    $scriptr.Options.AnsiFile = $false;
    $scriptr.Options.AllowSystemObjects = $false;
    $scriptr.Options.IncludeIfNotExists = $false;
    $scriptr.Options.NoCommandTerminator = $false;
    $scriptr.Options.ScriptBatchTerminator = $true;

    $null = $scriptr.Script($objectsToScript);

}
catch [Exception] {
    $e = $_.Exception
    $e.ToString();
    throw;
}