Trigger BEFORE INSERT from date to datetime

Question

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?

Answer 1

It seems each time you are inserting data into both tables i.e. price_changes and prices. Reverse process may be helpful. If possible try this :

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 ;

Then

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

• Lito: Yes, it's a possible solution, but prices must be inserted in prices table and not in prices_changes.
• Chandan: in your query manual entry will be done in prices table which triggered an entry inprices_changes .... in my case manual entry will be done in prices_changes table which triggered an entry to prices table..in both case two entry will be done but order will be changed.
• Lito: Yes, I know, but for some requirements the first INSERT must be prices. Anyway it's a possible solution. I don't undertand why the NEW.date has not the original hour. Maybe the NEW trigger values are coverted as original INSERT?
• Chandan: as per docs NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated. That's why it is truncating the time part first then insert to prices_changes