I have this schema:
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 |
+------------+---------------------+---------+
It should update prices
table with last value without product/date duplicates and insert into prices_changes
all updates using a trigger.
The problem is that in prices_changes
the date
column defined as datetime
is storing the date value without the time.
How can I store into the prices_changes
the data as datetime
using the original value?
Best Answer
It seems each time you are inserting data into both tables i.e.
price_changes
andprices
. Reverse process may be helpful. If possible try this :Then