T-SQL Update Trigger

413 views sql-server
9

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

answered question

I don't know why you get a syntax error, but cursors should be only used as a last resort since they are performance killers, and in triggers they should be avoided almost at all costs. You should rewrite your trigger to work on a set based approach.

1 Answer

1

try it like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] = N'trAfterUpdateInfoDoc' AND [type] = 'TR')
BEGIN
      -- your trigger creation code
END;

posted this

Have an answer?

JD

Please login first before posting an answer.