Agfdhyk

I'm trying to create the following trigger in SQL Server, but SSMS throws an error and I have no clue what it is. Any thoughts ?

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'trigger'.

Code:

IF NOT EXISTS(SELECT * FROM sys.triggers 

              WHERE object_id = OBJECT_ID(N'[dbo].[trAfterUpdateInfoDoc]'))

    CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]

    ON [dbo].[InfoDocs]

    AFTER UPDATE

    AS

    BEGIN

        DECLARE @infodoctemplateid INT;

        DECLARE @infodocid INT;

        DECLARE @requireccount FLOAT(2);

        DECLARE @filledcount FLOAT(2);

        DECLARE @pcnt FLOAT(2);



        DECLARE c CURSOR FOR

             SELECT id 

             FROM InfoDocs ifd 

             WHERE exists (SELECT 1 FROM Inserted AS i WHERE i.id = ifd.id)



        OPEN c



        FETCH NEXT FROM c INTO @infodocid



        WHILE @@Fetch_Status = 0 

        BEGIN

            SELECT @infodoctemplateid = InfoDocTemplateId 

            FROM InfoDocs 

            WHERE id = @infodocid;



            SELECT @requireccount = COUNT(*) 

            FROM InfoDocTemplateFields 

            WHERE InfoDocTemplateId = @infodoctemplateid 

              AND IsRequired = 1;



            IF (@requireccount = 0)

            BEGIN

                set @pcnt = 100;

            END

            ELSE

            BEGIN

                select @filledcount = count(*) from InfoDocFields 

                where InfoDocId = @infodocid 

                and InfoDocTemplateFieldId in (select id from InfoDocTemplateFields where InfoDocTemplateId = @infodoctemplateid and IsRequired = 1)

                and (BooleanValue is not null or (StringValue is not null and StringValue <> '') or IntValue is not null or DateValue is not null)



                set @pcnt = @filledcount / @requireccount * 100.0;

            END

            update InfoDocs set PercentageCompleted = @pcnt Where id = @infodocid;



            Fetch next From c into @infodocid

        End

    Close c

    Deallocate c

END

Create Trigger (Limitations section) must be the first statement in a batch, so you can't use the IF exists check before it.

In SQL Server 2016 SP1 onwards, you can use CREATE OR ALTER TRIGGER... for the same behaviour.

Pre-SQL Server 2016 SP1, there's some suggestions here

I also second Zohar's comment that putting this logic into a trigger could well cause you many performance issues & possibly hard to track down unexpected behaviour/bugs.

Anytime a SQL object like a trigger is created, it needs to be the only object created in the batch. A batch is terminated by the keyword GO.

Try refactoring your code to fit this general structure and see if it works:

IF OBJECT_ID(N'[dbo].[trAfterUpdateInfoDoc]') IS NOT NULL

    DROP TRIGGER [dbo].[trAfterUpdateInfoDoc]

GO



CREATE TRIGGER [dbo].[trAfterUpdateInfoDoc]

ON [dbo].[InfoDocs]

AFTER UPDATE

AS

BEGIN

    --PLACE CODE HERE

END

GO