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?

Solution 1

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

Solution 2

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)

Solution 3

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

Comments

[szpic]

I thought about droping and recreating but isn't better to alter instead of recreate? or this doesn't matter?

[Gordon Linoff]

@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]

@Sajad . . . Of course. Triggers are used like that all the time to maintain summary tables.

[Shafizadeh]

I think like you, but seems wrong. take a look here (part of Edit). no one does not answer me :( !

[David Browne - Microsoft]

Here's better authority to cite: docs.microsoft.com/en-us/sql/t-sql/statements/…

[workabyte]

they are the greater authority but that person who wrote the articular i ref is the ref i used and they deserve the credit ;)