Alter trigger if exists with use of fluent migrator

Question

I have one Trigger called dbo.SendMail and multiple database, not all database have the trigger dbo.SendMail.

I am using FluentMigrator to manage database versions and i want to do something like below

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SendMail]'))
BEGIN
    ALTER TRIGGER [dbo].[SendMail]
    ON [dbo].[Notification]
    FOR INSERT
    AS
    BEGIN
        some sql code
    END
END

it is giving me error Incorrect syntax near begin, Expecting EXTERNAL.

is there any way to achieve this ?

Thanks in advance.

Answer 1

Try this:

IF OBJECT_ID(N'[dbo].[SendMail]', N'TR') IS NOT NULL

-- Do whatever

Else

-- Do something else

Answer 2

Here is your trigger code with some dynamic sql. You have to roll with dynamic sql here because creating or altering objects must be the only statement in a batch. You can't wrap the create/alter logic inside an IF statement.

IF OBJECT_ID('SendMail') is not null
begin
    declare @SQL nvarchar(max)
    set @SQL = 'ALTER TRIGGER [dbo].[SendMail]
    ON [dbo].[Notification]
    FOR INSERT
    AS
    BEGIN
        some sql code
    END'

    exec sp_executesql @SQL
end