0

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?

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";
  • Yes, it's a possible solution, but prices must be inserted in prices table and not in prices_changes. – Lito Aug 11 '16 at 14:22
  • 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. – Chandan Aug 12 '16 at 5:13
  • 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? – Lito Aug 12 '16 at 7:04
  • 1
    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 – Chandan Aug 12 '16 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.