Executing Create Trigger throws sometimes an error depending on the query-structure including a vari-CodePudding

I have three TSQL-statements, each are supposed to create a trigger when executed.

I get only a partly parameterised statement to run. What i dont understand is, why SSMS is executing the one statement and throwing an error with the two others. Any help is much appreciated.

This does NOT work: executing the statement without any variable

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

This works: putting part of it into a variable

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

This does not work: putting all of the statement into a variable

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)

In both cases where it doesn't work i get the same error-message:

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

I am using:

SQL Server Management Studio 15.0.18424.0
Windows Operating System 10.0.22000
SQL-Server 12.0.2000.8

CodePudding user response：

CREATE TRIGGER must be the only statement in the batch. To address the problem:

option 1: Add a conditional DROP followed by a GO batch separator and 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

option 2: Use 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

option 3 (which you've already discovered): Use dynamic SQL so CREATE TRIGGER is in a separate batch.