MySQL Trigger to prevent INSERT under certain conditions

Question

I want to make a trigger that will prevent the insertion if the birthdate (one of the columns) is in the future. I have this:

CREATE TRIGGER foo
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
    ???mystery???
  END IF;
END;

What goes in the mystery part?

Answer 1

Based on this I'm not sure if it's possible to do it that way.

There's no support in MySQL's current implementation of triggers to voluntarily throw an exception and abort the statement that spawned the trigger.

The workaround I've found is to write a BEFORE trigger to set one of the not-NULL columns in the table to NULL, thus violating its NOT NULL constraint. This causes the statement that spawned the trigger to be aborted.

Answer 2

The problem described in the question sounds like a perfect candidate for a CHECK constraint - add this line to the table definition.

CONSTRAINT born_in_the_past CHECK (birthdate<=now())