Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL Trigger set datetime value using case statement

I'm using mysqlimport to do mass table inserts (replacing duplicates primary keys). Several tables have date time columns with records containing values '0000-00-00'. What I would like is a trigger which detects these '0000-00-00' values and replaces with '1950-01-01', otherwise keep the datetime value in the record (i.e. when it's not '0000-00-00').

I have tried the following:

CREATE TRIGGER set_datetime BEFORE INSERT ON tbl
FOR EACH ROW
CASE
WHEN date_col='0000-00-00' THEN SET date_col='1950-01-01';
END CASE

Thank you.

EDIT

Update attempt:

CREATE TRIGGER set_datetime BEFORE INSERT ON tbl
FOR EACH ROW
IF (NEW.date_col='0000-00-00') THEN
SET NEW.date_col='1950-01-01';
END IF;

Still getting an error in the SET portion.

EDIT 2

WORKING:

DELIMITER $$
CREATE TRIGGER insert_check BEFORE INSERT ON ar
FOR EACH ROW
BEGIN
    IF NEW.delivery_date='0000-00-00' THEN
        SET NEW.delivery_date='1950-01-01';
    END IF;
END; $$
DELIMITER ;

2 Answers

Use IF THEN :

IF (NEW.date_col = '0000-00-00') THEN
  SET NEW.date_col='1950-01-01';
END IF;
like image 184
a1ex07 Avatar answered Apr 17 '26 17:04

a1ex07


CREATE TRIGGER set_datetime BEFORE INSERT ON tbl 
FOR EACH ROW 
IF YEAR(NEW.date_col)=0 THEN
    SET NEW.date_col='1950-01-01 00:00:00'; 
END IF; 

Give it a Try !!!

like image 26
RolandoMySQLDBA Avatar answered Apr 17 '26 17:04

RolandoMySQLDBA



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!