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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`ticket_afterInsert`
	AFTER INSERT ON `ticket`
	FOR EACH ROW
BEGIN
	
	DECLARE vClientType VARCHAR(255);
	DECLARE vStateCode VARCHAR(255);
	DECLARE vTransferorFirstShipped DATE;

	-- Borrar cuando se cambie el insert ticket en la APP móvil

	SELECT typeFk INTO vClientType
		FROM vn.`client` WHERE id = NEW.clientFk;

	IF vClientType = 'loses' THEN
		SET vStateCode = 'DELIVERED';
	ELSE
		SET vStateCode = 'FREE';
	END IF;

	CALL ticket_setState(NEW.id, vStateCode);

	IF YEAR(NEW.shipped) > 2000 THEN

		SELECT cnb.firstShipped INTO vTransferorFirstShipped
				FROM bs.clientNewBorn cnb
					JOIN `client` c ON c.transferorFk = cnb.clientFk
				WHERE c.id = NEW.clientFk;

		INSERT INTO bs.clientNewBorn(clientFk, firstShipped, lastShipped)
			VALUES(NEW.clientFk, IFNULL(vTransferorFirstShipped, util.VN_CURDATE()), util.VN_CURDATE())
		ON DUPLICATE KEY UPDATE lastShipped = util.VN_CURDATE();
	
	END IF;

END$$
DELIMITER ;