Rockasaurus Rockasaurus -4 years ago 63
MySQL Question

Trigger to change empty values to NULL

I am trying to write a trigger where if an incoming value is empty (in other words ''), then insert NULL in the table. I have :

DELIMITER //
CREATE TRIGGER avoid_empty
BEFORE INSERT ON EVALUATION
FOR EACH ROW
BEGIN
IF mark = '' THEN SET NEW.mark = NULL;
END IF;
END;
//
DELIMITER ;


Which executes without errors, but it doesn't do what I need.

Answer Source

Try:

DELIMITER //

CREATE TRIGGER `avoid_empty` BEFORE INSERT ON `EVALUATION`
FOR EACH ROW
BEGIN
    IF NEW.`mark` = '' THEN
        SET NEW.`mark` := NULL;
    END IF;
END//

DELIMITER ;

See db-fiddle.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download