CODEWITHSUNDEEP
mysqltriggerssetcase
user320487
user320487

Reputation:

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 ;

Upvotes: 3

Views: 4803

Answers (3)

u628898
u628898

Reputation: 131

If you want, or need, to use 'case' then this should work:

SET NEW.date_col=CASE WHEN NEW.date_col='0000-00-00' THEN ='1950-01-01'
WHEN NEW.date_col='something else' THEN 'whatever'
ELSE 'default'
END CASE;

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

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 !!!

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

Use IF THEN :

IF (NEW.date_col = '0000-00-00') THEN
  SET NEW.date_col='1950-01-01';
END IF;

Upvotes: 3

Related Questions