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 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 
share|improve this question

1 Answer 1

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:

Since you are a newbie, writing trigger code requires changing the delimiter to something other than a semicolon and then changing back.

DELIMITER $$

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

DELIMITER ;

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

I am using above trigger with mysql server v 5.5.24 and it works fine. This is my local test site. Now I want to use it at my webhost, they have mysql server v 5.1.56 ... and it won; t work ! Am I missing something?

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 :

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

I directly quoted Pages 254-256 of the Book

dlckxmd

Please note the following:

Therefore, signal processing DOES NOT WORK IN MySQL 5.1.

You will have to do the following:

  • Rewrite the trigger in MySQL 5.1 to break midstream
  • The trigger you wrote in the MySQL 5.5 database should stay the way it is now

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

DELIMITER $$

CREATE TRIGGER chk_dates  
BEFORE INSERT ON verlof_aanvragen  
FOR EACH ROW  
BEGIN  
  DECLARE dummy INT;
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY) 
THEN  
  SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
END IF;
END $$

DELIMITER ;

Since mysql.user has no column called used, the query does not execute, breaking the trigger.

share|improve this answer
    
Thanks very much RolandoMySQLDBA !!! This did the trick almost. I changed it to: #START TRIGGER DELIMITER $$ 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 = 'Voor deze datum kan je niet aanvragen'; END IF ; END $$ DELIMITER ; #END TRIGGER And now it wrks very good :-) –  Hans Jun 9 '13 at 15:10
    
I am using above trigger with mysql server v 5.5.24 and it works fine. This is my local test site. Now I want to use it at my webhost, they have mysql server v 5.1.56 ... and it won; t work ! Am I missing something? –  Hans Jun 12 '13 at 15:06
    
I missed the extra semicolon. I'll add it to my answer. If my answer helped you, please mark it accepted. –  RolandoMySQLDBA Jun 12 '13 at 15:17
    
I addressed your last comment in my answer... –  RolandoMySQLDBA Jun 12 '13 at 15:38
    
Thanx again, this is the right coode. It works –  Hans Jun 14 '13 at 13:47

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.