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

发布时间：2020-12-12 16:37:16 所属栏目：MsSql教程来源：网络整理

导读：我正在使用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和

我正在使用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


 '

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!