我使用的是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 '更多推荐
如何添加“IF NOT EXISTS”创建触发器语句
发布评论