Create or alter trigger if exists

Question

I'm trying to determine withing if If I should create or alter and trigger. My Code is below.

IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    ALTER TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    AFTER INSERT
    AS RAISERROR ('Notify Compensation', 16, 10);
else
    CREATE TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    WITH ENCRYPTION
    AFTER INSERT, UPDATE 
    AS RAISERROR ('Notify Compensation', 16, 10);

The errors I'm getting are :

Incorrect syntax near else
Create trigger should be the only statement in batch.

How this code should look like?

Have a look at this - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/38684064-c9bd-4fc2-8c8c-7feb98b0252d/creating-a-trigger-inside-a-stored-procedure?forum=transactsql — Bulat, Aug 11 '15 at 11:51

score 14 · Accepted Answer · answered Aug 11 '15 at 11:58

14

If you don't want to the create trigger statement as dynamic SQL, then you can do something like this:

IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    exec sp_executesql N'DROP TRIGGER Sales.bonus_reminder';
GO

CREATE TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    WITH ENCRYPTION
    AFTER INSERT, UPDATE 
    AS RAISERROR ('Notify Compensation', 16, 10);

answered Aug 11 '15 at 11:58

Gordon Linoff

1,242,037
58
646
786

I thought about droping and recreating but isn't better to alter instead of recreate? or this doesn't matter? – szpic Aug 11 '15 at 12:05
@szpic . . . An `alter` would normally be better. For instance, when you drop a trigger and recreate it, there is a gap of time when the data could be modified and there is no trigger. In practice, though, I use triggers quite sparingly. Because they are part of "maintenance mode" on any system, I'm not worried about changes to the data. – Gordon Linoff Aug 11 '15 at 12:26
@Sajad . . . Of course. Triggers are used like that all the time to maintain summary tables. – Gordon Linoff Aug 11 '15 at 18:43
I think like you, but seems wrong. take a look [here](http://stackoverflow.com/questions/31920600/how-to-create-a-update-trigger-for-increase-decrease-1-number-to-total-votes-num) (part of Edit). no one does not answer me :( ! – Shafizadeh Aug 11 '15 at 18:46

workabyte · Answer 2 · 2020-04-24T16:25:26.980

3

Using this articular as my source of truth. here is the short answer.

As of SQL Server 2016 sp1 you can use create or alter statements instead of other drop and create methods (my personal fav till this) on some of the database objects (stored procedures/functions/triggers/views).

so your script could look like

create or alter TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    WITH ENCRYPTION
    AFTER INSERT, UPDATE 
    AS RAISERROR ('Notify Compensation', 16, 10)

edited Apr 24 '20 at 16:25

answered Apr 23 '20 at 22:39

workabyte

3,496
2
27
35

2

Here's better authority to cite: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2016 – David Browne - Microsoft Apr 24 '20 at 16:34
they are the greater authority but that person who wrote the articular i ref is the ref i used and they deserve the credit ;) – workabyte Apr 24 '20 at 16:41

score 2 · Answer 3 · answered Aug 11 '15 at 11:49

Use Dynamic SQL

IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    EXEC('
    ALTER TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    AFTER INSERT
    AS RAISERROR (''Notify Compensation'', 16, 10)'
    );
else
    EXEC('
    CREATE TRIGGER Sales.bonus_reminder
    ON Sales.SalesPersonQuotaHistory
    WITH ENCRYPTION
    AFTER INSERT, UPDATE 
    AS RAISERROR (''Notify Compensation'', 16, 10)'
    );

Create or alter trigger if exists

3 Answers3