代码之家 › 专栏 › 技术社区 › Ben

如何正确指定条件:SUM COUNT=列值?

triggers sql-server-2012 tsql sql-server

Ben · 技术社区 · 6 年前

我正在更新一个系统,试图创建一个原始表的“影子”表,通过触发器接收更改,但不使用原始表,因为我真的不喜欢原始程序的设计方式。

其中一个是团队配置表,包含所有用户及其团队ID。具有相同团队ID的用户属于同一团队。我正在尝试在这里创建触发器,以便将数据截取到新的团队历史记录日志中。(1行/1组ID) 但是,由于在团队设置之后,表中的多行将被更新,并且更新不是批量完成的,所以很难知道新团队设置何时结束。幸运的是,有这个成员计数列,它可以用来计算成员数,并确定与这个新团队ID相关的所有必要记录何时更新。

| team id     | uid | member_count |
|201701010800A| 1   | 3            |
|201701010800A| 2   | 3            |
|201701010800A| 3   | 3            |

我的问题是如何在更新后触发器中指定此条件。这是我现在要做的。但它看起来很粗糙。我想还有更合理的表达方式。

if exists(
    select 1 from ( values (1) ) t(c) 
    outer apply (
        select count(team_id) tc, max(member_count) mx, min(member_count) mm
        from old_teamtable 
        where team_id = ( select top 1 team_id from inserted )
    ) e
    where tc = mx and mx = mm
)

2 回复 | 直到 6 年前

HABO 6 年前

insert TeamsAndUsers TeamHistory IsComplete = 0 IsComplete 1

-- Create sample tables.
create table TeamsAndUsers ( Id Int Identity, TeamId Int, UserId Int, TeamMembers Int );

create table TeamHistory ( Id Int Identity, TeamId Int, IsComplete Bit );
go

-- Create the trigger.
create trigger UpdateTeamHistory
  on TeamsAndUsers
  after Insert
as
  set nocount on

  -- Handle any teams we've not heard of before.
  insert into TeamHistory ( TeamId, IsComplete )
    select distinct TeamId, 0
      from inserted
      where TeamId not in ( select TeamId from TeamHistory );

  -- See if any teams have been completed, i.e. all of their members are present.
  update TeamHistory
    set IsComplete = 1
    where TeamId in (
      select TAU.TeamId from (
        -- TeamId   values touched by the INSERT ...
        select distinct TeamId from inserted ) as i inner join
          -- ... matched with corresponding incomplete teams in the team history ...
          TeamHistory as TH on TH.TeamId = i.TeamId and TH.IsComplete = 0 inner join
          -- ... matched with users currently assigned to the teams.
          TeamsAndUsers as TAU on TAU.TeamId = i.TeamId
    group by TAU.TeamId
    having Count( TAU.UserId ) = Max( TAU.TeamMembers ) );
go

-- Test the trigger.
insert into TeamsAndUsers ( TeamId, UserId, TeamMembers ) values
  ( 101, 1, 3 ), ( 101, 2, 3 );
select * from TeamsAndUsers;
select * from TeamHistory;

insert into TeamsAndUsers ( TeamId, UserId, TeamMembers ) values
  ( 101, 3, 3 );
select * from TeamsAndUsers;
select * from TeamHistory;

insert into TeamsAndUsers ( TeamId, UserId, TeamMembers ) values
  ( 102, 4, 1 );
select * from TeamsAndUsers;
select * from TeamHistory;

insert into TeamsAndUsers ( TeamId, UserId, TeamMembers ) values
  ( 103, 10, 2 );
select * from TeamsAndUsers;
select * from TeamHistory;

insert into TeamsAndUsers ( TeamId, UserId, TeamMembers ) values
  ( 103, 11, 2 );
select * from TeamsAndUsers;
select * from TeamHistory;

select update

BoCoKeith 6 年前

if exists (
    select  1 
    from    old_teamtable 
    where   team_id = (select top 1 team_id from inserted) 
    having  count(*) = max(member_count) 
    and     max(member_count) = min(member_count)
)