sql-server - 我该如何纠正这个触发器？

Question

我在一个表上实现了一个触发器，它确保该表不包含超过一行，其中一列的位值为 1。但不知何故触发器无法正常工作。

create trigger [dbo].[flyingdutchman_needs_a_captain]
on [dbo].[flyingdutchman]
after insert, update, delete as
begin
set nocount on;
set rowcount 0;
begin try
declare @captainsum int
set @captainsum = 0     
set @captainsum = (select sum(case when uniontable.iscaptain = 1 then 1 else 0 end) 
from (
    select iscaptain from dbo.flyingdutchman 
    union all
    select inserted.iscaptain as iscaptain from inserted
    union all
    select deleted.iscaptain as iscaptain from deleted
    ) as uniontable
having sum(case when uniontable.iscaptain = 1 then 1 else 0 end)  <> 1)     
if(@captainsum <> 1)
begin
    throw 50000,'flying dutchman is cursed and needs a captain.',1;
end     
end try
begin catch
if xact_state()<>0
    rollback transaction;
throw;
end catch
end

flyingdutchman 是一个包含三列的表:

SailorId(int),SailorName(varchar),IsCaptain(bit)

和以下行:

执行查询时:

insert into dbo.flyingdutchman(Sailorname,IsCaptain)
values('Davy Jones',1)

我得到错误:

Msg 50000, Level 16, State 1, Procedure FLYINGDUTCHMAN_NEEDS_A_CAPTAIN, Line xx FLYING DUTCHMAN IS CURSED AND NEEDS A CAPTAIN.

我希望这个查询可以正常运行并且触发器不应该被触发。

Answer 1

因为这是一个after触发器，所以所有的更改都已经在表中了——你不需要查询inserted和deleted 表格 - 这就是您得到错误结果的原因。

你可以简单地这样做:

if 1 <> (select count(*) from dbo.flyingdutchman where iscaptain = 1)
    throw 50000,'flying dutchman is cursed and needs a captain.',1;

但是，我怀疑你应该改为这样做:

declare @captainsCount int;
select @captainsCount = count(*) from dbo.flyingdutchman where iscaptain = 1
if @captainsCount = 0 
    throw 50000,'flying dutchman is cursed and needs a captain.',1;
if @captainsCount > 1 
    throw 50000,'flying dutchman has too many captains.',1;