salix/db/routines/vn/triggers/collection_beforeUpdate.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`collection_beforeUpdate`
	BEFORE UPDATE ON `collection`
	FOR EACH ROW
BEGIN
	
	DECLARE vStateFk INT;
	DECLARE vTotalLines INT;
	DECLARE vPickedLines INT;

	IF NEW.saleTotalCount <= NEW.salePickedCount 
		AND (NEW.saleTotalCount != OLD.saleTotalCount 
			OR NEW.salePickedCount != OLD.salePickedCount)
		THEN 
		
			SELECT id INTO vStateFk
				FROM vn.state 
				WHERE code = 'PREPARED';
			
			SET NEW.stateFk = vStateFk;
	
	END IF;

	IF NEW.saleTotalCount > NEW.salePickedCount 
		AND (NEW.saleTotalCount != OLD.saleTotalCount OR NEW.salePickedCount != OLD.salePickedCount) THEN 

		SELECT id INTO vStateFk
			FROM vn.state 
			WHERE code = 'ON_PREPARATION';
		
		SET NEW.stateFk = vStateFk;
	
	END IF;

END$$
DELIMITER ;