3

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?

4

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.

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

enter image description here

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

  • Very appreciated. – RCNeil Dec 11 '12 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.