如何创建一个减少计数器的触发器，我的触发器出了什么问题？

I need to decrease a counter in a table schedules, when there was an insert in enrollments table:

当注册表中有插入时，我需要减少表计划中的计数器：

CREATE TRIGGER [UpdateEnrollmentsTrigger]
ON [TBLENROLLMENT_ENR]
FOR INSERT
AS
BEGIN
    DECLARE @ScheduleCode NVARCHAR
    DECLARE @TotalSlots INT

    IF EXISTS(SELECT SCH_CODE FROM inserted)
        BEGIN
            SELECT @ScheduleCode = SCH_CODE FROM inserted

            SELECT @TotalSlots = SCH_TOTALSLOTS FROM TBLSCHEDULES_SCH
            WHERE SCH_CODE = @ScheduleCode

            UPDATE TBLSCHEDULES_SCH
            SET SCH_FREESLOTS = @TotalSlots - 1
            WHERE SCH_CODE = @ScheduleCode
        END
END

When I trying to create this trigger, the query window of VS12 says:

当我尝试创建此触发器时，VS12的查询窗口显示：

SQL46010 :: Incorrect syntax near ].

Thanks in advance.

提前致谢。

1 个解决方案

#1

The specific error is because you are using FOR INSERT instead of AFTER INSERT, but there are other things that you should improve on your trigger.

特定错误是因为您使用的是FOR INSERT而不是AFTER INSERT，但是还有其他一些事情需要改进。

First of all, always, always write the length of a NVARCHAR, leaving it blank will behave differently depending where it's used. So replace DECLARE @ScheduleCode NVARCHAR with DECLARE @ScheduleCode NVARCHAR(n), where n is the required length.

首先，始终始终写入NVARCHAR的长度，将其留空将根据其使用位置而有所不同。所以用DECLARE @ScheduleCode NVARCHAR（n）替换DECLARE @ScheduleCode NVARCHAR，其中n是所需的长度。

I'm also not sure why you are doing the IF EXISTS since it's reading the INSERTED pseudo table, that it's bound to have records because the trigger was fired.

我也不确定你为什么要做IF EXISTS，因为它正在读取INSERTED伪表，因为触发器被触发它必然会有记录。

Another thing to improve is that you are assuming that only one row was inserted, as you are storing it on a variable, that's wrong and it will behave incorrectly if you insert more than just one row.

另一件需要改进的地方是你假设只插入了一行，因为你将它存储在一个变量上，这是错误的，如果你插入的不仅仅是一行，它就会表现不正确。

Oh, I almost forgot, you should also always specify the schema, for instance: CREATE TRIGGER [UpdateEnrollmentsTrigger] ON [TBLENROLLMENT_ENR] should be CREATE TRIGGER dbo.[UpdateEnrollmentsTrigger] ON dbo.[TBLENROLLMENT_ENR] (using the correct schema, of course)

哦，我差点忘了，你也应该总是指定架构，例如：CREATE TRIGGER [UpdateEnrollmentsTrigger] ON [TBLENROLLMENT_ENR]应该是CREATE TRIGGER dbo。[UpdateEnrollmentsTrigger] ON dbo。[TBLENROLLMENT_ENR]（当然使用正确的架构）

#1