Trigger - preventing insert of an end date before its begin date

[Axeia]

Fairly simply question, how to prevent the insertion of a beginning date that starts before its end?

 

CREATE TRIGGER trg_Series_prevent_invalid_end_date 
  BEFORE INSERT ON Series
    FOR EACH ROW 
    BEGIN
      IF(NEW.air_end_date < NEW.end_air_start_date OR NEW.air_start_date IS NULL AND NEW.air_end_date IS NOT NULL ) THEN
          INSERT INTO EpisodeReleaseDate VALUES( 'xxx', 'xxx', 'xxx' ); --RaiseError (MySQL doesn't support raise error)
      END IF;
END$$

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

 

The EpisodeReleaseDate table consist out of 3 fields, of which the first one is an INT, so inserting a string should throw some error.. which seems to be way how you're supposed to raise an error in MySQL till v6 is out

 

Starting to think it would be the trouble to simply migrate to PostgreSQL.. but was just getting the hang of MySQL workbench

[fenway]

Simplify your IF -- and check the syntax.

[Axeia]

The if can't be simplified. It should prevent the insertion of an end date while no begin date is known.

And it should prevent the insertion of an end date that begins before a start date.  (or disallow start dates that begin after the end date depending on how you look at it).

 

I tried removing the ';' tried adding the thing on different places, tried copy/pasting a working trigger to adept it so it fits my needs but nothing works and I'm not seeing the syntax problem with this one.

If only MySQL supported check constraints..

[andrewgauger]

Did you execute :

Delimiter $$

before you ran the trigger?

[fenway]

The if can't be simplified.

If it's broken, debugging = simplying.  This is why no one can solve problems anymore.

[Mchl]

This is strange, but it seems that reason is in the comment. At least on my machine, it worked fine, once I added extra space after --.

 

[Axeia]

This is strange, but it seems that reason is in the comment. At least on my machine, it worked fine, once I added extra space after --.

Ladies and gentlemen, we have a winner.

Very peculiar, would have never found that, thanks

 

Did move on to PostgreSQL now though due to this and some other reasons.. where it was as simple as

ALTER TABLE AnimeSeries
ADD CONSTRAINT chk_prevent_incorrect_date_entries_in_series
CHECK( air_start_date IS NULL AND air_end_date IS NOT NULL OR air_end_date < air_start_date )

 

 

[edit]

Perhaps not so strange as it is mentioned in the manual.

From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.5.6, “'--' as the Start of a Comment”.

So basically they're deviating from the standard

[Mchl]

Everyone does

I usually do /**/ comments in my MySQL so I wasn't really aware of that.