jeudi 29 janvier 2015

How to add "IF NOT EXISTS" to create trigger statement

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


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

Aucun commentaire:

Enregistrer un commentaire