CODEWITHSUNDEEP
mysql
clarkk
clarkk

Reputation: 27685

trigger update multiple tables

I need to update two child tables when the parent is updated

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

error

duplicate handler declared in the same block

Upvotes: 0

Views: 55

Answers (1)

Barmar
Barmar

Reputation: 780879

Combine your two DECLARE CONTINUE HANDLER statements into a single statement that sets both variables.

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

Since the first loop will SET done_module = TRUE, you need to set it back to FALSE before the second loop.

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

Upvotes: 1

Related Questions