Zak Zak - 7 months ago 9
SQL Question

MySQL need help setting up trigger

I am trying to set up a

TRIGGER
to clear empty strings before
INSERT
. Not rocket science but I can't find the error in my syntax!

Here is the
TRIGGER
itself

USE `ga_abtest_logging`;
DELIMITER $$
CREATE TRIGGER avoid_empty
BEFORE INSERT ON daily_analytics
FOR EACH ROW
BEGIN
IF profileID = ''
THEN SET profileID = NULL
END IF;
END$$


Here is what workbench is showing:

DELIMITER

On hover over the
END IF
it reads
syntax error, unexpected END, expecting ';'


Could I have a problem with the settings on my DB? I have gone through the MySQL docs and I think the trigger looks right! Does anyone see anything obviously wrong?

Answer

You should make a few changes:

  • Use the NEW. prefix when referencing a column value
  • Add a semi-colon at the end of the line where you set the value

For example:

DELIMITER $$

CREATE TRIGGER tr_b_ins_daily_analytics BEFORE INSERT ON daily_analytics FOR EACH ROW BEGIN
  IF (NEW.profileID = '')
  THEN
    SET NEW.profileID = NULL;
  END IF;
END $$

DELIMITER ;