DELIMITER $$;
CREATE TRIGGER on_insert_avoir_commercial_code 
AFTER INSERT ON entetecommercials
    FOR EACH ROW 
        IF (NEW.type = 'avoir' AND NEW.code_commande is not null) THEN
        BEGIN
            DECLARE done INT DEFAULT FALSE;
            DECLARE var_user_code CHAR(100);

            DECLARE lignes CURSOR FOR SELECT user_code 
                                      FROM entetecommercials 
                                      WHERE code = NEW.code_commande;

            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            OPEN lignes;

            read_loop: LOOP
            
                FETCH lignes INTO var_user_code;
                IF done THEN
                    LEAVE read_loop;
                END IF;
                
                UPDATE entetecommercials SET commercial_code=var_user_code WHERE code=NEW.code;
            
            END LOOP;
            CLOSE lignes;
        END;
        END IF;