我正在使用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(Build 7601：Service Pack 1)上的Microsoft Corporation标准版(64位) )(管理程序).我是新来的sql server和procedure / triggers.我有以下代码创建一个触发器(它的工作原理)：
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一起工作.这是我失败的努力之一：
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
根据你的更新问题…尝试这个：
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 '
