title: How to get trigger text correctly in SQL Server?
body: <p>Given:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>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:</p>
  
  <ol>
  <li><code>sys.sql_modules</code> view</li>
  <li><code>sys.syscomments</code> view</li>
  <li><code>OBJECT_DEFINITION</code> function</li>
  <li><code>sp_helptext</code> function</li>
  </ol>
  
  <p>All the four ways yield the same text for the trigger:</p>
  
  <pre><code>CREATE TRIGGER Y ON X FOR DELETE AS SET NOCOUNT ON;
  </code></pre>
  
  <p>And that is my problem, of course, because this text cannot be used to create the trigger - table <code>X</code> no longer exists.</p>
  
  <p>Now, if I select the trigger in the SSMS (on table Z, of course) and script the CREATE action, here is what I get:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which is correct, unlike the previous 4 ways.</p>
  
  <p>So, my question is - how do I obtain the correct trigger text? The one we can run to recreate the trigger. Like SSMS does.</p>