Comparing dates in a BEFORE INSERT trigger

Question

I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.

Something like:

CREATE TRIGGER chk_dates
   BEFORE INSERT ON `job_history`
   FOR EACH ROW
    BEGIN
      IF( NEW.end_date < OLD.start_date ) .../// error, don't insert data
      ELSE .../// allow the data to be entered

My table job_history will have a start_date, and when end_date is entered, I want it to check to ensure that it isn't prior to start_date.

How do I accomplish this?

Answer 1

With MySQL 5.5 or 5.6, you can use SIGNAL instead of the hacks to bail from a trigger.

Also, on a BEFORE INSERT trigger, only the NEW alias is used. Not OLD.

CREATE TRIGGER chk_dates
BEFORE INSERT ON `job_history`
FOR EACH ROW
BEGIN
  IF (NEW.end_date < NEW.start_date) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'end_date cannot be earlier than start_date';
  END IF;
END;

No "else" is needed because taking no action in a BEFORE trigger allows the original action to proceed.

Answer 2

I am not very proud of what I am about to show you but it must be done

Back on April 25, 2011 I wrote this post : Trigger in MySQL to prevent insertion

It explains how to interrupt a trigger midstream. You do so by deliberately launching an SQL statement that is syntactically correct but breaks on execution. This still was originally suggested on pages 254-256 of the book

because SIGNAL processing is not properly implemented (OK it wasn't implemented a lick)

With regard to your something like code, it should look something like this:

CREATE TRIGGER chk_dates
   BEFORE INSERT ON `job_history`
   FOR EACH ROW
    BEGIN
      DECLARE x INT;
      IF( NEW.end_date < OLD.start_date ) THEN
          SELECT dummy INTO x FROM mysql.user WHERE 1=1 LIMIT 1;
      ELSE .../// allow the data to be entered

Give it a Try !!!