mysql trigger to insert/update date column from datetime column

Question

probably simple question but can't get it to work:

I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them.

Now, i would like to, whenever a row is inserted or updated, to extract the date part from the datetime columns, and insert or update the date columns.

Not very well versed with triggers, so pointers would be much appreciated.

server version: 5.1.49-3

Answer 1

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
 (`id` int not null auto_increment primary key, 
 `dt1` datetime, 
 `dt2` datetime, 
 `d1` date, 
 `d2` date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
 SET NEW.d1 = DATE(NEW.dt1), 
 NEW.d2 = DATE(NEW.dt2);
CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
 SET NEW.d1 = DATE(NEW.dt1), 
 NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo