对于递归关系的表,如何做到级联更新

Question

RT设有表:
USE `test`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` 
(
 `id` int(11) COMMENT '主键',
 `parent` int(11) COMMENT '父节点',
 `name` varchar(20) DEFAULT NULL COMMENT '名称',
 `cost` float DEFAULT NULL COMMENT '成本',
 `budget` float DEFAULT NULL COMMENT '预算',
 `progress` float DEFAULT NULL COMMENT '进度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
ALTER TABLE `test` ADD CONSTRAINT PK_TEST1 PRIMARY KEY (`id`);
ALTER TABLE `test` ADD CONSTRAINT FK_TEST1_PARENT_TEST FOREIGN KEY(`parent`) 
REFERENCES test(`id`);     
INSERT INTO `test`(`id`,`name`) VALUES(-1,'结束标记');
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (1,-1,'复杂任务1',200);
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (2,1,'复杂任务1-子项--简单任务2',100);
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (3,1,'复杂任务1-子项--简单任务3',100);
事务:
  更新3
   update `test` set cost =50 where id =3;
  期望的结果:
  id parent name cost budget progress
  1 -1 '复杂任务1'   50 200      0.25
  2 1 '复杂任务1-子项--简单任务2'     null 100 null
  3     1 '复杂任务1-子项--简单任务3' 50      100 0.5
  
  更新2
   update `test` set cost =50 where id =2;
  期望的结果:
  id parent name cost budget progress
  1 -1 '复杂任务1'   100 200      0.5
  2 1 '复杂任务1-子项--简单任务2'     50 100 0.5
  3     1 '复杂任务1-子项--简单任务3' 50      100 0.5已有实现:delimiter //
-- 求指定节点的总成本。(总成本=子节点的累加和)
DROP FUNCTION IF EXISTS test_sum//
CREATE FUNCTION test_sum(p_parent int) RETURNS FLOAT
BEGIN
DECLARE result FLOAT DEFAULT 0.0;
SELECT SUM(cost) into result FROM test where id in(select id from test where parent = p_parent);
       IF(result IS NULL) then 
 set result = 0;
       END IF ;
RETURN result;
end//-- 更新指定节点的进度,并更新此节点的所有父节点的成本和进度
drop function if exists getParentList;//
create function getParentList(childId int) returns varchar(1000)
begin
declare sTemp varchar(1000);
declare sTempPar int;
declare l_cost float default 0;
declare l_budget float default 0;
declare l_parent int;
set sTemp = '$';
set sTempPar = childId;
select cost into l_cost from test where id=childId;
select budget into l_budget from test where id=childId;
UPDATE test SET progress = l_cost/l_budget where id = childId;
while sTempPar != -1 do
select budget into l_budget from test where id = sTempPar;
select parent into l_parent from test where id = sTempPar;
set sTemp = concat(sTemp,',',l_parent);
update test set cost=test_sum(l_parent) where id=l_parent;
IF(sTempPar != childId) then
UPDATE test SET progress = test_sum(sTempPar)/l_budget where id=sTempPar;
end if;
select group_concat(parent) into sTempPar from test where parent<>id and find_in_set(id,sTempPar)>0;
end while;
return sTemp;
end//
delimiter ;语句序列:        update `test` set cost =50 where id =3;
select  getParentList(3);
update `test` set cost =50 where id =2;
select  getParentList(2); 问题:
 语句序列不合预期,期望语句序列:
 update `test` set cost =50 where id =3; 
 update `test` set cost =50 where id =2;
 如果使用触发器实现，则在插入/更新的时机,不能修改同一张表中的数据。以上。求指导,求扫盲。多谢。

Answer 1

①做此表的一个备份test_back
②做一道test_bak的触发器,在test_bak表改变时候,备份test_bak到test_bak。并调用函数。
③操作时针对test_back,查询时针对test。这样做,显然开销比较大。

Answer 2

②做一道test_bak的触发器,在test_bak表改变时候,备份test_bak到test。并调用函数。

Answer 3

核心就是将test_bak当做test表的一个外观。用来接收参数,转发语句。test_bak的数据一致性甚至都不需要额外的精力维护。

Answer 4

取消外键
更新时语句保证顺序
增加对parent的validation