如何添加“泪不存在”以创建触发语句

小编典典

如何添加“泪不存在”以创建触发语句

sql

我正在使用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（内部版本7601：Service Pack 1）上的Microsoft Corporation标准版（64位））（管理程序）。我是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

仅当触发器不存在时，如何有条件地创建？我在这里做错了什么？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

阅读 165

2021-03-17

共1个答案

小编典典

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


 '

2021-03-17