從日期到日期時間在插入之前觸發

我有這個架構：

CREATE TABLE `prices` (
 `id_product` int(11) unsigned NOT NULL,
 `date` date NOT NULL, # Important: DATE
 `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
 PRIMARY KEY (`id_product`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prices_changes` (
 `id_product` int(11) unsigned NOT NULL,
 `date` datetime NOT NULL, # Important: DATETIME
 `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
 KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER `prices_before_insert`
 BEFORE INSERT ON `prices`
 FOR EACH ROW
   INSERT INTO `prices_changes` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`);;
DELIMITER ;

INSERT INTO `prices` SET
 `id_product` = 1,
 `date` = "2016-08-11 13:50:00", # Is a DATETIME value
 `value` = "20.50"
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

mysql> SELECT * FROM `prices` WHERE id_product = 1;
+------------+------------+---------+
| id_product | date       | value   |
+------------+------------+---------+
| 1          | 2016-08-11 | 20.50   |
+------------+------------+---------+

mysql> SELECT * FROM `prices_changes` WHERE id_product = 1;
+------------+---------------------+---------+
| id_product | date                | value   |
+------------+---------------------+---------+
| 1          | 2016-08-11 00:00:00 | 20.50   |
+------------+---------------------+---------+

它應該使用沒有產品/日期重複的最後一個值更新表，並使用觸發器prices插入所有更新。prices_changes

問題是在定義為prices_changes的列中儲存沒有時間的日期值。date``datetime

如何使用原始值儲存到prices_changes數據中？datetime

似乎每次您將數據插入兩個表時price_changes，即prices. 反向過程可能會有所幫助。如果可能的話試試這個：

DELIMITER ;;
CREATE TRIGGER `prices_changes_before_insert`
  BEFORE INSERT ON `prices_changes`
  FOR EACH ROW
  INSERT INTO `prices` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`)
   ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);;
DELIMITER ;

然後

INSERT INTO `prices_changes` SET
  `id_product` = 3,
   `date` = "2016-08-12 14:50:00", # Is a DATETIME value
   `value` = "20.50";

引用自：https://dba.stackexchange.com/questions/146502