DROP TRIGGER IF EXISTS UpdateRowNumberOnInsertConf;
DELIMITER //
CREATE TRIGGER UpdateRowNumberOnInsertConf AFTER INSERT ON configurazione
FOR EACH ROW
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE crow INTEGER DEFAULT(1);
	DECLARE curid INTEGER;
	DECLARE cur CURSOR FOR SELECT id FROM configurazione WHERE codice = NEW.codice;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET crow = 1;	
	SET done = FALSE;
	OPEN cur;
		ins_loop: LOOP
			FETCH cur INTO curid;
			IF done THEN
				LEAVE ins_loop;
			END IF;
			INSERT INTO 
				colori_configurazione (colore_id, container_id) 
			VALUES(crow, curid) 
			ON DUPLICATE KEY UPDATE colore_id = crow;
			SET crow = crow + 1;
		END LOOP;
	CLOSE cur;
END//

DROP PROCEDURE IF EXISTS PUpdateRowNumberOnInsertConf;
DELIMITER //
CREATE PROCEDURE PUpdateRowNumberOnInsertConf(in acodice VARCHAR(100))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE crow INTEGER DEFAULT(1);
	DECLARE curid INTEGER;
	DECLARE cur CURSOR FOR SELECT id FROM configurazione WHERE codice = acodice;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET crow = 1;	
	SET done = FALSE;
	OPEN cur;
		ins_loop: LOOP
			FETCH cur INTO curid;
			IF done THEN
				LEAVE ins_loop;
			END IF;
			INSERT INTO 
				colori_configurazione (colore_id, container_id) 
			VALUES(crow, curid) 
			ON DUPLICATE KEY UPDATE colore_id = crow;
			SET crow = crow + 1;
		END LOOP;
	CLOSE cur;
END//

DROP TRIGGER IF EXISTS UpdateRowNumberOnInsertConfTrasp;
DELIMITER //
CREATE TRIGGER UpdateRowNumberOnInsertConfTrasp AFTER INSERT ON configurazione_trasporti
FOR EACH ROW
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE crow INTEGER DEFAULT(1);
	DECLARE curid INTEGER;
	DECLARE cur CURSOR FOR SELECT id FROM configurazione_trasporti WHERE codice = NEW.codice;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET crow = 1;	
	SET done = FALSE;
	OPEN cur;
		ins_loop: LOOP
			FETCH cur INTO curid;
			IF done THEN
				LEAVE ins_loop;
			END IF;
			INSERT INTO 
				colori_configurazione_trasporti (colore_id, trasporti_id) 
			VALUES(crow, curid) 
			ON DUPLICATE KEY UPDATE colore_id = crow;
			SET crow = crow + 1;
		END LOOP;
	CLOSE cur;
END//

DROP PROCEDURE IF EXISTS PUpdateRowNumberOnInsertConfTrasp;
DELIMITER //
CREATE PROCEDURE PUpdateRowNumberOnInsertConfTrasp(in acodice VARCHAR(100))
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE crow INTEGER DEFAULT(1);
	DECLARE curid INTEGER;
	DECLARE cur CURSOR FOR SELECT id FROM configurazione_trasporti WHERE codice = acodice;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET crow = 1;	
	SET done = FALSE;
	OPEN cur;
		ins_loop: LOOP
			FETCH cur INTO curid;
			IF done THEN
				LEAVE ins_loop;
			END IF;
			INSERT INTO 
				colori_configurazione_trasporti (colore_id, trasporti_id) 
			VALUES(crow, curid) 
			ON DUPLICATE KEY UPDATE colore_id = crow;
			SET crow = crow + 1;
		END LOOP;
	CLOSE cur;
END//
