CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
szpic
szpic

Reputation: 4496

Create or alter trigger if exists

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 :

How this code should look like?

Upvotes: 12

Views: 17739

Answers (3)

workabyte
workabyte

Reputation: 3765

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)

Upvotes: 9

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 17

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 3

Related Questions