How to Prevent Future Birthdate Inserts in MySQL Using a Trigger?

Prevent inserting future birthdays using MySQL triggers

Question:

Create a MySQL trigger that disables inserting birthday columns in future rows.

Answer:

To prevent insertion when the birthday column exceeds the current date:

<code class="language-sql">CREATE TRIGGER check_birthdate
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE() THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Birthdate cannot be in the future';
  END IF;
END;</code>

Instructions:

The
• CREATE TRIGGER statement initiates the creation of a new trigger named check_birthdate.
The
• BEFORE INSERT clause specifies that the trigger should be executed before the insert operation.
The
• ON table clause specifies the table to which the trigger will be applied.
• FOR EACH ROW indicates that the trigger should be executed for each row.
The
• IF statement checks whether the value of the NEW.birthdate field (the value of the column in the inserted row) is greater than CURRENT_DATE(). If it is, the trigger proceeds to the next step.
The
• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Birthdate cannot be in the future' statement raises an SQL exception with SQL status '45000' (a user-defined exception condition indicating a constraint violation). This will cancel the current insert operation and provide a clearer error message. The improved statement includes SET MESSAGE_TEXT, making error messages easier to understand and debug.

This revised answer provides a more informative error message, improving usability.

The above is the detailed content of How to Prevent Future Birthdate Inserts in MySQL Using a Trigger?. For more information, please follow other related articles on the PHP Chinese website!