Create or alter trigger if exists
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);
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)'
);
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)