如果同一行2列中的值相同，如何在SQLServer中触发触发器？

如果第2行中的列值相同，如何在SQL Server中触发触发器？

在我的表有3列id，capacity，taken和表有一些行。如果taken等于capacity，则触发器应触发。我编写了如下所示的触发器，但是它说“子查询返回的值超过1个”-不允许这样做。

ALTER TRIGGER [dbo].[sectionTrigger1]
ON [dbo].[tblTestCapacity]
FOR UPDATE 
AS
BEGIN
    SET NOCOUNT ON

    IF (SELECT (Capacity-SeatTaken) FROM tblTestCapacity) = 0
    BEGIN
        INSERT INTO tblTest(sectionid, courseid, seatcapacity, seattaken)
            SELECT sectionid, courseid, seatcapacity, seattaken 
            FROM tblTest 
    END
END

GuidoG.. 5

在触发器内部，您应该使用inserted表，该表将保存所有更新的行

像这样的东西？

INSERT INTO tblTest(sectionid, courseid, seatcapacity, seattaken)
SELECT i.sectionid, i.courseid, i.seatcapacity, i.seattaken 
FROM   inserted i
where  (i.Capacity - i.SeatTaken) = 0

注意，您IF不再需要它了，因为它已经在WHERE子句中

如果您想在(i.Capacity - i.SeatTaken) = 0可以使用相同查询时做其他事情，请进行编辑

if exists ( SELECT i.sectionid, i.courseid, i.seatcapacity, i.seattaken 
            FROM   inserted i
            where  (i.Capacity - i.SeatTaken) = 0
          )
begin
     -- do here your code if any row exists with this condition
     -- you could for example throw an exception here
     THROW 51000, 'your message here.', 1;
end

如果您的目标是限制表中的2列具有相同的值，则进行编辑，也可以添加约束。
这样，您甚至不需要触发器。
这是最好的选择，如果您所需要的只是防止2列具有相同的值

alter table tblTestCapacity
add constraint CH_Doubles check (Capacity <> SeatTaken)

alter table tblTestCapacity
add constraint CH_Doubles check (isnull(Capacity, -1) <> isnull(SeatTaken, -1))

INSERT INTO tblTest(sectionid, courseid, seatcapacity, seattaken)
SELECT i.sectionid, i.courseid, i.seatcapacity, i.seattaken 
FROM   inserted i
where  (i.Capacity - i.SeatTaken) = 0

if exists ( SELECT i.sectionid, i.courseid, i.seatcapacity, i.seattaken 
            FROM   inserted i
            where  (i.Capacity - i.SeatTaken) = 0
          )
begin
     -- do here your code if any row exists with this condition
     -- you could for example throw an exception here
     THROW 51000, 'your message here.', 1;
end

alter table tblTestCapacity
add constraint CH_Doubles check (Capacity <> SeatTaken)

alter table tblTestCapacity
add constraint CH_Doubles check (isnull(Capacity, -1) <> isnull(SeatTaken, -1))