如何通过单脚本动态执行多条CREATE TRIGGER语句？

我需要在同一动态SQL脚本中批量创建多个触发器，但直接拼接多个CREATE TRIGGER语句执行会报错，以下是测试代码及报错情况：

declare @commands varchar(max) = '
    drop table if exists dbo.Countries
    drop table if exists dbo.Cities

    create table dbo.Countries(
        id int identity not null primary key,
        Country varchar(50) not null
    )

    create table dbo.Cities(
        id int identity not null primary key,
        City varchar(50) not null
    )
'
print @commands

/* 这段执行正常 */
execute (@commands)
go

declare @commands varchar(max) = '
    create trigger tr_Countries on
        dbo.Countries for insert as
    begin
        print ''A new country was created.''
    end

    create trigger tr_Cities on
        dbo.Cities for insert as
    begin
        print ''A new city was created.''
    end
'
print @commands

/* 执行失败，报错：
Msg 156, Level 15, State 1, Procedure tr_Countries, Line 8 [Batch Start Line 20]
Incorrect syntax near the keyword 'trigger'. 
*/
execute (@commands)

问题原因 #

SQL Server规定，CREATE TRIGGER语句必须作为独立批处理的第一条语句执行。直接将多个CREATE TRIGGER拼接在同一个字符串中执行时，它们属于同一个批处理，第二个CREATE TRIGGER不是批处理的第一条语句，因此触发语法错误。而CREATE TABLE等语句无此限制，所以第一个批量脚本可正常执行。

解决方案 #

要在单个动态SQL字符串中批量创建触发器，需将每个CREATE TRIGGER语句包装为独立的EXEC调用，确保每个触发器的创建都在独立批处理中执行。

基础实现示例 #

declare @commands varchar(max) = '
    EXEC(''create trigger tr_Countries on dbo.Countries for insert as begin print ''''A new country was created.'''' end'');
    EXEC(''create trigger tr_Cities on dbo.Cities for insert as begin print ''''A new city was created.'''' end'');
'
print @commands
execute (@commands)

适配元数据维护场景 #

如果是通过查询生成批量触发器创建语句（如元数据维护场景），可修改命令生成逻辑，将每个触发器的创建语句包装为EXEC调用后再拼接成单个字符串：

declare @Commands varchar(max)

/* 获取需要处理的对象列表 */
;with QueryObjectsYouNeed as (
    select 'Countries' TableName union all
    select 'Cities' TableName
),
/* 生成每个触发器的创建命令并包装为EXEC调用 */
CommandsList as (
    select 'EXEC(''create trigger tr_' + TableName + ' on dbo.' + TableName + ' for insert as begin print ''''A new ' + lower(TableName) + ' was created.'''' end'');' Command
        from QueryObjectsYouNeed
)
/* 将所有命令拼接为单个字符串 */
select @Commands = string_agg(cast(Command as varchar(max)), char(13))
    from CommandsList

print @Commands
/* 执行动态SQL */
Execute(@Commands)

这种方式既满足了“单个字符串执行”的需求，又避免使用临时表、循环或游标，同时确保每个触发器的创建都符合SQL Server的批处理规则。

内容的提问来源于stack exchange，提问作者FcoJavier99