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?

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

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