SQL 如何添加“IF NOT EXISTS”来创建触发器语句

How to add "IF NOT EXISTS" to create trigger statement

提问by thebiggestlebowski

I am using sql server 2008 R2. More specifically, Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor). I am new to sql server and procedures/triggers. I have the following code to create a trigger (it works):

我正在使用 sql server 2008 R2。更具体地说，Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 2010 年 4 月 2 日 15:48:46 版权所有 (c) Microsoft Corporation Standard Edition（64 位），Windows NT 6.1（内部版本 7601：Service Pack 1） )（管理程序）。我是 sql server 和程序/触发器的新手。我有以下代码来创建触发器（它有效）：

CREATE TRIGGER [dbo].[Insert_WithdrawalCodes] 
   ON  [dbo].[PupilWithdrawalReason] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
        UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME() 
        WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted)
END

How do I conditionally create only if the trigger does not yet exist? What am I doing wrong here? StackOverflow has good examples of IF NOT EXISTS, but I can't get this to work in conjunction with a CREATE. Here is one of my failed efforts:

仅当触发器尚不存在时，如何有条件地创建？我在这里做错了什么？StackOverflow 有很好的例子IF NOT EXISTS，但我不能让它与CREATE. 这是我失败的努力之一：

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'Insert_WithdrawalCodes')
   exec('CREATE TRIGGER [dbo].[Insert_WithdrawalCodes] ON  [dbo].[PupilWithdrawalReason] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME() WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted) END')
GO

回答by JStevens

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGERNAME]'))
DROP TRIGGER [dbo].[TRIGGERNAME]
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TABLENAME]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE   TRIGGER [dbo].[TRIGGERNAME] ON [dbo].[TABLENAME] FOR INSERT, UPDATE 

AS ...

END

Based on your updated question... try this:

根据您更新的问题...试试这个：

IF NOT EXISTS (select * from sys.objects where type = 'TR' and name = 'Insert_WithdrawalCodes')
EXEC dbo.sp_executesql @statement = N'

CREATE TRIGGER [dbo].[Insert_WithdrawalCodes] 
   ON  [dbo].[PupilWithdrawalReason] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
        UPDATE [dbo].[PupilWithdrawalReason] SET DateCreated=dbo.SYSTEMTIME() 
        WHERE WithdrawalCodeID IN (SELECT WithdrawalCodeID FROM inserted)
END


 '

回答by M.Ali

The best way is to check for objects and drop them if they exist before you create them.

最好的方法是在创建对象之前检查对象并删除它们（如果它们存在）。

Rather then not creating it at all if it exists, I would approach it the other way, drop it if exists and then create.

如果它存在，则根本不创建它，我会以另一种方式接近它，如果存在则删除它然后创建。

Normally in long lenghty scripts if you want to update the definition of a trigger you would just simply add this at the end of that script and your trigger definition will be updated.

通常在长脚本中，如果您想更新触发器的定义，您只需在该脚本的末尾添加它，您的触发器定义就会更新。

So the approach should be create the object but drop it if it already existsrather then dont create it at all if it already exists

所以方法应该是create the object but drop it if it already exists而不是dont create it at all if it already exists

IF OBJECT_ID ('[Insert_WithdrawalCodes] ', 'TR') IS NOT NULL
   DROP TRIGGER [Insert_WithdrawalCodes];
GO

CREATE TRIGGER .......

回答by Neel

Certain statements like CREATE TRIGGER needs to be the first in a batch (as in, group of statements separated by GO ).

某些语句如 CREATE TRIGGER 需要是批处理中的第一个（例如，由 GO 分隔的语句组）。

https://msdn.microsoft.com/en-us/library/ms175502.aspx

https://msdn.microsoft.com/en-us/library/ms175502.aspx

Alternatively you could do this

或者你可以这样做

IF NOT EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   type = 'TR'
                    AND name = 'Insert_WithdrawalCodes' ) 
BEGIN
    EXEC ('CREATE TRIGGER Insert_WithdrawalCodes ON ...');
END;

回答by lifestyle

As other answers above not mentioned an important point I wrote this answer:

由于上面的其他答案没有提到一个重点，我写了这个答案：

• When we want to find a trigger or another object in sys.objectstable, it is better to check accurately (with schema or object_id, etc) in where clause to avoid same name invalid results. Consider when another trigger with the same name already exists in another schema... Therefore, because the sys.objecttable contains an schema_idcolumn, we can use it in addition to nameand typecolumns to query more accurately as I provided as example below.

• As Microsoft docs mentioned hereunder "Trigger Limitations":

• 当我们要在sys.objects表中查找触发器或其他对象时，最好object_id在 where 子句中进行准确检查（使用 schema 或等），以避免出现同名无效结果。考虑当另一个模式中已经存在另一个同名触发器......因此，因为该sys.object表包含一个schema_id列，我们可以使用它name和type列一起使用它来更准确地查询，如下面的示例所示。

• 正如此处在“触发器限制”下提到的 Microsoft 文档：

CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。

therefore we use EXECUTEto overcome this limitation:

因此我们使用EXECUTE来克服这个限制：

IF NOT EXISTS (select * from sys.objects where schema_id=SCHEMA_ID('dbo') AND type='TR' and name='Insert_WithdrawalCodes')
BEGIN
   EXECUTE ('CREATE TRIGGER [Insert_WithdrawalCodes] ON [dbo].[PupilWithdrawalReason]
   AFTER INSERT
   AS 
   BEGIN
      SET NOCOUNT ON;
      ...
   END');
END

回答by Jatin Phulera

Check IF Exist For Trigger

检查是否存在触发器

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = 
                 N'[Trigger_Name]' AND [type] = 'TR')
 BEGIN
 DROP TRIGGER [Trigger_Name]
 Print('Trigger dropped => [Schema].[Trigger_Name]')
 END
 GO

Check IF Exist for Stored procedure , Function also by clicking below link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html

检查 IF Exist for Stored procedure , Function 也通过点击下面的链接 http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html