
DROP TRIGGER IF EXISTS `AU_ShopArticleItem_Buyable`;

DELIMITER |

CREATE TRIGGER AU_ShopArticleItem_Buyable AFTER UPDATE ON ShopArticleItem
	FOR EACH ROW BEGIN
		IF NEW.ShopArticleItem_Buyable != OLD.ShopArticleItem_Buyable THEN
			IF NEW.ShopArticleItem_Buyable = 'true' THEN
				UPDATE ShopArticle SET
					ShopArticle.ShopArticle_Buyable = 'true'
				WHERE
					ShopArticle.ShopArticle_ID = NEW.ShopArticle_ID
					AND ShopArticle.ShopArticle_Buyable != 'true';
			ELSEIF NEW.ShopArticleItem_Buyable = 'false' THEN
				UPDATE ShopArticle SET
					ShopArticle.ShopArticle_Buyable = 'false'
				WHERE
					ShopArticle.ShopArticle_ID = NEW.ShopArticle_ID
					AND ShopArticle.ShopArticle_Buyable != 'false'
					AND 0 = (
						SELECT COUNT(*) FROM ShopArticleItem AS sai WHERE sai.ShopArticle_ID = NEW.ShopArticle_ID AND sai.ShopArticleItem_Buyable = 'true'
					);
			END IF;
		END IF;
	END;
|
	
DELIMITER ;


-- ------------------------------------------------
-- data initialization
-- ------------------------------------------------
UPDATE ShopArticle SET ShopArticle.ShopArticle_Buyable = 'true';
UPDATE ShopArticle SET
	ShopArticle.ShopArticle_Buyable = 'false'
WHERE
	0 = (
		SELECT COUNT(*) FROM ShopArticleItem AS sai WHERE sai.ShopArticle_ID = ShopArticle.ShopArticle_ID AND sai.ShopArticleItem_Buyable = 'true'
	);
	
	