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

本文介绍了如何添加“IF NOT EXISTS”创建触发器语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用的是sql server 2008 R2。更具体地说，Microsoft SQL Server 2008 R2（RTM） - 10.50.1600.1（X64）2010年4月2日15:48:46版权所有（c）Windows NT 6.1上的Microsoft Corporation标准版（64位）（Build 7601：Service Pack 1） ）（管理程序）。我是sql server和procedures / triggers的新手。我有以下代码来创建一个触发器（它可以工作）：

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):

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

如果触发器尚不存在，我如何有条件地创建？我在这做错了什么？ Stackoverflow有很好的如果不存在的例子，但我不能让它与CREATE一起工作。这是我失败的努力之一：

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:

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

推荐答案

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

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

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 '