基于子表计数更新父列的触发器 [英] Trigger to Update Parent Column Based on Child Table Count

问题描述

我有一张桌子叫:

Home

HomeID, HomeAddress, Capacity.

另一个叫:

Children

ChildID, HomeID,Childname.

有没有一种方法可以让我自动更新每个 Home 表容量列，并将其链接为 Children 表中的外键?

Is there a way in which i can auto update each Home table capacity column with the amount of children it has linked as a foreign key in the Children table ?

推荐答案

您必须使用 2 个触发器才能实现.

You have to use 2 triggers for this to achieve.

1. 更新儿童信息后
2. 删除儿童后

示例 1:更新后:

Example 1: After UPDATE:

delimiter //

drop trigger if exists au_on_children //

create trigger au_on_children after update on children 
for each row
begin
  declare old_totalCapacity int not null default 0;
  declare new_totalCapacity int not null default 0;

  select 
    case when homeID = OLD.homeID 
              then sum( OLD.homeID ) 
         else sum( homeID ) 
     end 
    into old_totalCapacity ,
    case when homeID = NEW.homeID 
              then sum( NEW.homeID ) 
         else sum( homeID )
     end 
    into new_totalCapacity 
    from children;

  update home 
     set capacity =  
         case when homeID = OLD.homeID 
                   then old_totalCapacity  
              else capacity 
         end ,
         case when homeID = NEW.homeID 
                   then new_totalCapacity 
              else capacity 
         end ;
end;
//

delimiter ;

示例 1:删除后:

Example 1: After DELETE:

delimiter //

drop trigger if exists ad_on_children //

create trigger ad_on_children after delete on children 
for each row
begin
  declare totalCapacity int not null default 0;

  select sum( homeID ) 
    into totalCapacity 
    from children 
   where homeID = OLD.homeID;

  update home 
     set capacity = totalCapacity 
   where homeId = OLD.homeID;
end;
//

delimiter ;

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