 -- AUTO_INCREMENT für Tabelle `food_order`
 --
 ALTER TABLE `food_order`
-  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
+  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
 --
 -- AUTO_INCREMENT für Tabelle `product`
 --
 ALTER TABLE `product`
-  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
+  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
 --
 -- AUTO_INCREMENT für Tabelle `product_line`
 --

--

INSERT INTO `product_price_log` (`id`, `product_id`, `field`, `price`, `valid_from`, `valid_to`) VALUES
(7, 3, 'price_buy', '3.30', '2016-05-03 20:29:35', '2016-05-03 20:29:55'),
(8, 3, 'price_buy', '3.50', '2016-05-03 20:29:55', '2016-05-03 20:35:56'),
(9, 3, 'price_sell', '3.70', '2016-05-03 20:29:58', '2016-05-03 20:32:58'),
(10, 4, 'price_sell', '8.70', '2016-05-03 20:32:58', NULL),
(12, 4, 'price_buy', '8.20', '2016-05-03 20:36:00', NULL);

-- --------------------------------------------------------


--

INSERT INTO `product` (`id`, `name`, `price_buy`, `price_sell`, `created`, `modified`) VALUES
(5, 'Döner mild', '3.40', '3.50', '2016-05-04 08:22:49', '2016-05-04 08:22:49'),
(6, 'Döner scharf', '3.40', '3.50', '2016-05-04 08:23:00', '2016-05-04 08:23:00'),
(7, 'Pizza Salami', '8.40', '8.60', '2016-05-04 08:23:14', '2016-05-04 08:23:14');

--
-- Trigger `product`
--
DELIMITER $$
CREATE TRIGGER `after_insert_product` AFTER INSERT ON `product` FOR EACH ROW BEGIN
	INSERT INTO product_price_log (id, product_id, field, price, valid_from, valid_to) 
		VALUES (NULL, NEW.id, 'price_sell', NEW.price_sell, NOW(), NULL);
	INSERT INTO product_price_log (id, product_id, field, price, valid_from, valid_to) 
		VALUES (NULL, NEW.id, 'price_buy', NEW.price_buy, NOW(), NULL); 
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_update_products` AFTER UPDATE ON `product` FOR EACH ROW BEGIN
        IF NEW.price_sell <> OLD.price_sell THEN         	UPDATE product_price_log SET valid_to = NOW() WHERE field = 'price_sell' AND valid_to IS NULL;             INSERT INTO product_price_log (id, product_id, field, price, valid_from, valid_to) VALUES (NULL, NEW.id, 'price_sell', NEW.price_sell, NOW(), NULL);         END IF;
        
        IF NEW.price_buy <> OLD.price_buy THEN         	UPDATE product_price_log SET valid_to = NOW() WHERE field = 'price_buy' AND valid_to IS NULL;             INSERT INTO product_price_log (id, product_id, field, price, valid_from, valid_to) VALUES (NULL, NEW.id, 'price_buy', NEW.price_buy, NOW(), NULL);         END IF;
    END
$$
DELIMITER ;
