I am new to MySQL and I'm having problems creating a BEFORE INSERT
trigger.
I get an unexpected END
error.
I have a table named verlof_aanvragen
with a column datum
(and 6 more columns).
What I want to accomplish is only rows with a date between now()+8
and now()+365
or CURDATE() + INTERVAL 8 DAY AND CURDATE() + INTERVAL 365 DAY)
can be inserted. An insert with a date outside that interval should fail.
I am using MySQL version 5.5.24.
This is the code:
CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'
END IF
END
Best Answer
I have dealt with this question before. MySQL has a very flimsy architecture when it comes to triggers.
I have written posts before on how to break triggers midstream. The solution is rather unsavory and would leave a pure SQL developer with a bad taste in the month.
With that said, here are my posts that I hope would help:
Apr 25, 2011
: Trigger in MySQL to prevent insertionDec 23, 2011
: check constraint does not work?Since you are a newbie, writing trigger code requires changing the delimiter to something other than a semicolon and then changing back.
Please look back at my posts to see how I do this in my code.
UPDATE 2013-06-12 11:34 EDT
Here is your last comment
The problem is the MySQL version. If you look back in my two trigger posts, I used black market techniques to break the trigger midstream. This is necessary for older versions of MySQL. Notice what I stated on
Apr 25, 2011
in Trigger in MySQL to prevent insertion :I directly quoted Pages 254-256 of the Book
Please note the following:
Therefore, signal processing DOES NOT WORK IN MySQL 5.1.
You will have to do the following:
That's why the trigger does not work in MySQL 5.1.
UPDATE 2013-06-12 11:44 EDT
Here is what you can do to the trigger in the MySQL 5.1.56 database
Since
mysql.user
has no column calledused
, the query does not execute, breaking the trigger.