CODEWITHSUNDEEP
sql-serverdatabase-trigger
mark
mark

Reputation: 62784

How to get trigger text correctly in SQL Server?

Given:

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'

This simple query creates a table, puts a trigger on it, renames the table and uses four different ways to obtain the text of the trigger:

  1. sys.sql_modules view
  2. sys.syscomments view
  3. OBJECT_DEFINITION function
  4. sp_helptext function

All the four ways yield the same text for the trigger:

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

And that is my problem, of course, because this text cannot be used to create the trigger - table X no longer exists.

Now, if I select the trigger in the SSMS (on table Z, of course) and script the CREATE action, here is what I get:

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

Which is correct, unlike the previous 4 ways.

So, my question is - how do I obtain the correct trigger text? The one we can run to recreate the trigger. Like SSMS does.

Upvotes: 3

Views: 1165

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46213

SSMS uses SMO for scripting, which changes the ON clause of the CREATE TRIGGER to match that of the parent table. You could use a SMO PowerShell script like the example below to get the same text as SSMS generates. Just tweak the PS script for the generation options you want.

The trigger text apparently got out of sync because the table was renamed without recreating the trigger. 

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;
}

Upvotes: 2

Related Questions