1

I have to automatically script the triggers of my tables in a database. I am getting a syntax error for the trigger script when creating the stored procedure text. Copying the trigger definition text into a query window in SQL Server shows that the definition does not contain CRLF characters in the script most of the definition is on the comment line.

Is there anyway to add new line characters into the trigger definition text. I get the trigger definition using this query:

SELECT trig.name AS "@TriggerName", REPLACE(OBJECT_DEFINITION(trig.object_id), '''', '''''')  AS "@TrigDefinition"
FROM sys.triggers trig
WHERE trig.type = 'TR'

The text of the trigger defintion of a text trigger looks like this:

-- =============================================  -- Author:  <Author,,Name>  -- Create date: <Create Date,,>  -- Description: <Description,,>  -- =============================================  
    CREATE TRIGGER TestTrigger2     ON  BundleProduct      AFTER DELETE,UPDATE  AS   BEGIN   -- SET NOCOUNT ON added to prevent extra result sets from   -- interfering with SELECT statements.   SET NOCOUNT ON;        -- Insert statements for trigger here   select * from Edging;  END

How can I add new line characters to the trigger script so that I can add it to a stored procedure that is created from a query?

UPDATE I tried adding newline characters (CHAR(13) + CHAR(10) to the trigger definition using the REPLACE string function:

DECLARE @NewLine varchar(2) = CHAR(13) + CHAR(10);
REPLACE(TrigDefinition, '--', @NewLine + '--')

So before every comment I add new line characters but it does not work.

Gloria Santin
  • 2,066
  • 3
  • 51
  • 124
  • See this link http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string – Sagar Shelke Sep 20 '16 at 17:44
  • That looks like it will add a new line character in every space in the text string. I tried doing this: `REPLACE(TrigDefintion, '--', @NewLine + '--')` but it did not work. – Gloria Santin Sep 20 '16 at 17:49
  • how are copying generated trigger definition script to file I mean are you copying it manually Copy/Paste? If so try **CTRL + T** before running your Original query in SQL Server Management Studio. – Sagar Shelke Sep 20 '16 at 18:18
  • New line characters doesn't show in result grid in sql management studio. You can right click and change result to file or text or copy data from grid and paste into file... – steryd Sep 20 '16 at 18:18
  • Thanks for that information. So showing as 'Results To Text' displays the trigger with new line characters. So that might not be the problem. But when my query creates the stored procedure and tries to write the trigger definition as a stored procedure I get the error, `Incorrect syntax near the keyword 'TRIGGER'.` I get this for each of the 3 triggers that I am scripting. Any ideas as to why this error is occurring? – Gloria Santin Sep 20 '16 at 18:33
  • @gloria-santin I'm just trying to guess you might want add Go after each trigger definition. – Sagar Shelke Sep 20 '16 at 18:39
  • Just figured it out...I need to surround the `CREATE TRIGGER` in `EXEC('...)`. Now it works. – Gloria Santin Sep 20 '16 at 18:41

2 Answers2

1

Use CHAR(10) to insert Newline characters into SQL Strings.

If desired, you can also use CHAR(13) to insert Carriage Return characters, so CHAR(13)+CHAR(10) would be a CRLF.

You are not going to be able to fix your current string with a replace. You'll need to construct the desired string by hand and update your table with it.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • How do I identify the end of a comment line? Why is this a problem with trigger definitions but not with other definitions like views and stored procedures? – Gloria Santin Sep 20 '16 at 18:04
  • There is no rule that you can use to identify the end of a comment line. That's why a human has to read it and make a judgement about what is a comment and what is code. This problem isn't unique to triggers, so it must have been something different about the way your triggers were originally stored in the table. btw, this may be an XY problem, and there may be an easier way to do whatever it is you're really trying to do. – Tab Alleman Sep 20 '16 at 18:19
0

Added EXEC( [Trigger Definition] ). Now it can be added to the stored procedure.

Gloria Santin
  • 2,066
  • 3
  • 51
  • 124