T-SQL更新触发器 [英] T-SQL Update Trigger

问题描述

我正在尝试在SQL Server中创建以下触发器，但是SSMS会引发错误，我不知道它是什么.有什么想法吗?

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 ?

第15层状态1的第2行156消息
关键字触发"附近的语法不正确.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'trigger'.

代码:

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.

在SQL Server 2016 SP1及更高版本中，可以将CREATE或ALTER TRIGGER ...用于相同的行为.

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

SQL Server 2016 SP1之前的版本，有一些建议(

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

我还佐佐尔(Zohar)评论说，将这种逻辑触发会很容易引起许多性能问题，可能很难跟踪意外的行为/错误.

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.

这篇关于T-SQL更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！