gpt4 book ai didi

mysql - 触发更新多个表

转载 作者:行者123 更新时间:2023-11-29 11:19:28 25 4
gpt4 key购买 nike

当父表更新时,我需要更新两个子表

BEGIN
DECLARE done_bank INT DEFAULT FALSE;
DECLARE done_module INT DEFAULT FALSE;
DECLARE ids_bank INT;
DECLARE ids_module INT;
DECLARE cur_bank CURSOR FOR SELECT id AS ids_bank FROM accounting_bank WHERE accounting_id = NEW.id;
DECLARE cur_module CURSOR FOR SELECT id AS ids_module FROM accounting_module WHERE accounting_id = NEW.id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_bank = TRUE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_module = TRUE;

IF NEW.is_booked <> OLD.is_booked THEN
OPEN cur_bank;
ins_loop_bank: LOOP
FETCH cur_bank INTO ids_bank;
IF done_bank THEN
LEAVE ins_loop_bank;
END IF;

UPDATE accounting_bank SET is_booked=NEW.is_booked WHERE id=ids_bank;
END LOOP;
CLOSE cur_bank;

OPEN cur_module;
ins_loop_module: LOOP
FETCH cur_module INTO ids_module;
IF done_module THEN
LEAVE ins_loop_module;
END IF;

UPDATE accounting_module SET is_booked=NEW.is_booked WHERE id=ids_module;
END LOOP;
CLOSE cur_module;
END IF;
END

错误

duplicate handler declared in the same block

最佳答案

将两个 DECLARE CONTINUE HANDLER 语句合并为一个设置这两个变量的语句。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_bank = TRUE, done_module = TRUE;

由于第一个循环将SET did_module = TRUE,因此您需要在第二个循环之前将其设置回FALSE

BEGIN
DECLARE done_bank INT DEFAULT FALSE;
DECLARE done_module INT DEFAULT FALSE;
DECLARE ids_bank INT;
DECLARE ids_module INT;
DECLARE cur_bank CURSOR FOR SELECT id AS ids_bank FROM accounting_bank WHERE accounting_id = NEW.id;
DECLARE cur_module CURSOR FOR SELECT id AS ids_module FROM accounting_module WHERE accounting_id = NEW.id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_bank = TRUE, done_module = TRUE;

IF NEW.is_booked <> OLD.is_booked THEN
OPEN cur_bank;
ins_loop_bank: LOOP
FETCH cur_bank INTO ids_bank;
IF done_bank THEN
LEAVE ins_loop_bank;
END IF;

UPDATE accounting_bank SET is_booked=NEW.is_booked WHERE id=ids_bank;
END LOOP;
CLOSE cur_bank;

SET done_module = FALSE;
OPEN cur_module;
ins_loop_module: LOOP
FETCH cur_module INTO ids_module;
IF done_module THEN
LEAVE ins_loop_module;
END IF;

UPDATE accounting_module SET is_booked=NEW.is_booked WHERE id=ids_module;
END LOOP;
CLOSE cur_module;
END IF;
END

关于mysql - 触发更新多个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39181982/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com