Eu criei este procedimento armazenado que cria dinamicamente o mesmo gatilho para todas as minhas tabelas:

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Drop Stored Procedure
BEGIN TRY
     DROP PROCEDURE [dbo].[sp_CreateDataChangedTrigger]
END TRY
BEGIN CATCH
END CATCH
GO

--Create Stored Procedure

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================

-- =============================================
-- Author:      Scott Bass
-- Create date: 06JUL2014
-- Description: Create Data Change triggers
-- =============================================
CREATE PROCEDURE sp_CreateDataChangedTrigger
    -- Add the parameters for the stored procedure here
    @TableName varchar(255), 
    @TableKey  varchar(255),
    @Debug     bit=1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

     DECLARE @SQL varchar(max);

     SET @SQL = '
--Drop Trigger
BEGIN TRY
     DROP TRIGGER [dbo].[TR_' + @TableName + '_Audit]
END TRY
BEGIN CATCH
END CATCH
GO

--Create Trigger
CREATE TRIGGER [dbo].[TR_' + @TableName + '_Audit]
     ON [dbo].[' + @TableName + ']
     AFTER INSERT, UPDATE, DELETE
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @event_type [char]

     --Get Event Type
     IF EXISTS(SELECT * FROM INSERTED)
     IF EXISTS(SELECT * FROM DELETED)
          SELECT @event_type = ''U''
     ELSE
          SELECT @event_type = ''I''
     ELSE
     IF EXISTS(SELECT * FROM deleted)
          SELECT @event_type = ''D''
     ELSE
     --no rows affected - cannot determine event
          SELECT @event_type = ''K''

     IF @event_type IN (''I'',''U'') BEGIN
          DECLARE @CurrentUserID INT;
          SELECT  @CurrentUserID = u.UserID
          FROM    [dbo].[dim_Users] u
          WHERE   u.[Username] = dbo.udfUserName()

          UPDATE  t
          SET     DateModified = GETDATE(),
                  WhoModifiedID = @CurrentUserID
          FROM    INSERTED e
          JOIN    [dbo].[' + @TableName + '] t ON e.[' + @TableKey + '] = t.[' + @TableKey + ']
     END

     IF @event_type = ''D'' BEGIN
          no_op:  --Nothing for now
     END
END
GO
';

     IF @Debug=1 BEGIN
          set nocount on;
          print @SQL;
     END
     ELSE BEGIN
          exec(@SQL);
     END    
END
GO

Se eu chamar o SP com a opção de depuração:

SET NOCOUNT ON;

DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_CreateDataChangedTrigger]
        @TableName = N'dim_Status',
        @TableKey = N'StatusID',
        @Debug = 1

SELECT  'Return Value' = @return_value

GO

Em seguida, envie os resultados da janela Mensagens, ele funciona bem.

Mas, quando desligo o interruptor @Debug, recebo estas mensagens de erro:

Mensagem 102, nível 15, estado 1, linha 10
Sintaxe incorreta perto de 'GO'.
Mensagem 111, nível 15, estado 1, linha 13
'CREATE TRIGGER' deve ser a primeira instrução em um lote de consulta.
Mensagem 102, Nível 15, Estado 1, Linha 51
Sintaxe incorreta perto de 'GO'.

Obrigado...