根据包含变量的查询结构，执行Create Trigger有时会引发错误

Question

我有三个TSQL语句，每个语句都应该在执行时创建一个触发器。

我只能运行一个部分参数化的语句。我不明白的是，为什么SSMS执行一条语句，并与另外两条语句一起抛出错误。非常感谢您的帮助。

1. 这不起作用：在没有任何变量的情况下执行语句

IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND SCHEMA_NAME(schema_id) = 'D365_del' AND [name] = 'trg_Table_del') 
BEGIN
CREATE TRIGGER [D365].[trg_Table_del] ON  [D365].[Table] AFTER DELETE AS INSERT INTO [D365_del].[Table] ([ID], [Action],[ModifiedDate])(SELECT [ID], 1,SYSDATETIME() from DELETED)
END

2. 这很有效：将其一部分放入变量中

declare @SQL nvarchar(4000)
set @SQL = 'CREATE TRIGGER [D365].[trg_Table_del] ON  [D365].[Table] AFTER DELETE AS INSERT INTO [D365_del].[Table] ([ID], [Action],[ModifiedDate])(SELECT [ID], 1,SYSDATETIME() from DELETED) '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND SCHEMA_NAME(schema_id) = 'D365_del' AND [name] = 'trg_Table_del') 
BEGIN
EXEC (@SQL)
END

3. 这不起作用：将所有语句放入变量中

declare @SQL nvarchar(4000)
set @SQL = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = ''TR'' AND SCHEMA_NAME(schema_id) = ''D365_del'' AND [name] = ''trg_Table_del'') BEGIN CREATE TRIGGER [D365].[trg_Table_del] ON  [D365].[Table] AFTER DELETE AS INSERT INTO [D365_del].[Table] ([ID], [Action],[ModifiedDate])(SELECT [ID], 1,SYSDATETIME() from DELETED) END'
EXEC (@SQL)

在这两种情况下，它都不起作用，我得到相同的错误消息：

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TRIGGER'.

我正在使用：

• SQL Server Management Studio 15.0.184244.0
• Windows操作系统10.0.22000
• SQL Server 2008年12月12日

Answer 1

CREATE TRIGGER必须是批处理中的唯一语句。解决问题：

选项1：添加条件DROP，然后添加GO批处理分隔符和CREATE TRIGGER:

IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND SCHEMA_NAME(schema_id) = 'D365_del' AND [name] = 'trg_Table_del') 
BEGIN
DROP TRIGGER [D365].[trg_Table_del].
END
GO
CREATE TRIGGER [D365].[trg_Table_del] ON  [D365].[Table] 
AFTER DELETE AS
INSERT INTO [D365_del].[Table] ([ID], [Action],[ModifiedDate])
(SELECT [ID], 1,SYSDATETIME() from DELETED)
GO

选项2：使用CREATE OR ALTER:

CREATE OR ALTER TRIGGER [D365].[trg_Table_del] ON  [D365].[Table] 
AFTER DELETE AS
INSERT INTO [D365_del].[Table] ([ID], [Action],[ModifiedDate])
(SELECT [ID], 1,SYSDATETIME() from DELETED)
GO

选项3(您已经发现(：使用动态SQL，使CREATE TRIGGER处于单独的批处理中。