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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`ticketCollection_afterDelete`
	AFTER DELETE ON `ticketCollection`
	FOR EACH ROW
BEGIN 
	
	DECLARE vSalesRemaining INT;

	SELECT count(*) INTO vSalesRemaining
		FROM vn.ticketCollection tc
			JOIN sale s ON s.ticketFk = tc.ticketFk 
		WHERE collectionFk = OLD.collectionFk
			AND tc.id != OLD.id;
		
	IF NOT vSalesRemaining THEN
	
		DELETE FROM vn.collection WHERE id = OLD.collectionFk;
	
	ELSE
	
		UPDATE vn.collection 
			SET saleTotalCount = vSalesRemaining
			WHERE id = OLD.collectionFk;
		
	END IF;
		
END$$
DELIMITER ;