我有三個 TSQL 陳述句，每個陳述句在執行時都應該創建一個觸發器。
我只能運行一個部分引數化的陳述句。我不明白的是，為什么 SSMS 正在執行一個陳述句并與另外兩個陳述句一起引發錯誤。任何幫助深表感謝。
- 這不起作用：在沒有任何變數的情況下執行陳述句
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
- 這有效：將其中的一部分放入變數中
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
- 這不起作用：將所有陳述句放入變數中
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 管理作業室 15.0.18424.0
- Windows 作業系統 10.0.22000
- SQL 服務器 12.0.2000.8
uj5u.com熱心網友回復：
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在單獨的批次中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514572.html