How to add "IF NOT EXISTS" to create trigger statement
Solution 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
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
'
Solution 2
The best way is to check for objects and drop them if they exist before you create them.
Rather then not creating it at all if it exists, I would approach it the other way, drop it if exists and then create.
Normally in long lenghty scripts if you want to update the definition of a trigger you would just simply add this at the end of that script and your trigger definition will be updated.
So the approach should be create the object but drop it if it already exists
rather then dont create it at all if it already exists
IF OBJECT_ID ('[Insert_WithdrawalCodes] ', 'TR') IS NOT NULL
DROP TRIGGER [Insert_WithdrawalCodes];
GO
CREATE TRIGGER .......
Solution 3
Certain statements like CREATE TRIGGER needs to be the first in a batch (as in, group of statements separated by GO ).
https://msdn.microsoft.com/en-us/library/ms175502.aspx
Alternatively you could do this
IF NOT EXISTS ( SELECT *
FROM sys.objects
WHERE type = 'TR'
AND name = 'Insert_WithdrawalCodes' )
BEGIN
EXEC ('CREATE TRIGGER Insert_WithdrawalCodes ON ...');
END;
Solution 4
As other answers above not mentioned an important point I wrote this answer:
When we want to find a trigger or another object in
sys.objects
table, it is better to check accurately (with schema orobject_id
, etc) in where clause to avoid same name invalid results. Consider when another trigger with the same name already exists in another schema... Therefore, because thesys.object
table contains anschema_id
column, we can use it in addition toname
andtype
columns to query more accurately as I provided as example below.As Microsoft docs mentioned here under "Trigger Limitations":
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.
therefore we use EXECUTE to overcome this limitation:
IF NOT EXISTS (select * from sys.objects where schema_id=SCHEMA_ID('dbo') AND type='TR' and name='Insert_WithdrawalCodes')
BEGIN
EXECUTE ('CREATE TRIGGER [Insert_WithdrawalCodes] ON [dbo].[PupilWithdrawalReason]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
...
END');
END
Related videos on Youtube
thebiggestlebowski
Updated on July 09, 2022Comments
-
thebiggestlebowski almost 2 years
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
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 aCREATE
. 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
-
thebiggestlebowski over 9 yearsI updated my failed example. Why doesn't the "exec" code work? Is my syntax wrong in the create?
-
-
thebiggestlebowski over 9 yearsI really do want to use "if not exists". This question has some answers which explain why "if not exists" is better. stackoverflow.com/questions/937908/….
-
thebiggestlebowski over 9 yearsI tried this, but it didn't work. I'm going to update my question with the code which didn't work - would be great if you can spot why it isn't working. stackoverflow.com/questions/937908/….
-
thebiggestlebowski over 9 yearsI really do want to use "if not exists". This question has some answers which explain why "if not exists" is better. stackoverflow.com/questions/937908/….
-
thebiggestlebowski over 9 yearsThanks for your efforts. I tried that code and got this: Error 330 SQL03070: This statement is not recognized in this context.
-
JStevens over 9 yearsWhat version of SQL are you in?
-
thebiggestlebowski over 9 yearsI added the version to my original question.
-
JStevens over 9 yearsYour latest code throws no errors for me. What error do you get?
-
thebiggestlebowski over 9 yearsIt isn't working when I deploy the database project through visual studio. However, in sql management server this works, so I'm marking as accepted. Thanks!
-
Neel over 9 yearsYou are missing a semi-colon after EXEC(). Try this 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');
-
thebiggestlebowski over 9 yearsThank you for noticing that!
-
thebiggestlebowski over 9 yearsThis is the error: "Error 330 SQL03070: This statement is not recognized in this context."
-
Robert Sievers over 7 yearsNote that for DDL triggers you must query sys.server_triggers and not sys.triggers. It took me a while to figure that out.
-
Y.B. over 6 yearsFor those looking for an explanation:
if not exists
allows to verify the object is indeed a trigger. -
youcantryreachingme over 5 yearsIf your trigger definition contains literal strings (delimited by single quotes) this will break your nvarchar value being supplied to <at>statement. To circumvent this, after your IF statement I added BEGIN and END clauses. Within those I have declare <at>statement nvarchar(max); set <at>statement = N'create trigger .... ' + nchar(39) + N'Your literal string value within the trigger definition, in here' + nchar(39) + N' .... remainder of trigger definition;' EXEC dbo.sp_executesql <at>statement; You'll need this funky nchar stuff for each literal string in your trigger definition.
-
AgentFire almost 5 yearsThis is underrated.