Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you for being up to date. +1 !!! –  RolandoMySQLDBA Dec 11 '12 at 9:42
    
thanks for clarifying. This is very helpful! –  RCNeil Dec 11 '12 at 13:20
add comment

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

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

share|improve this answer
    
Very appreciated. –  RCNeil Dec 11 '12 at 3:13
    
Another alternative to emulate check constraints in MySQL here: wikis.oracle.com/display/mysql/… –  Paul White Dec 11 '12 at 6:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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