1

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

2

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

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.