How to Use a MySQL Trigger to Prevent Future Birthdate Inserts?

Use a MySQL trigger to prevent future birth dates from being inserted

MySQL allows the use of triggers to prevent insert operations based on specific conditions. One such case is to prevent inserts with birthdates in the future.

To do this, you can create a BEFORE INSERT trigger that checks the value of the birthdate column:

<code class="language-sql">CREATE TRIGGER foo BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
    -- 如何阻止插入？
  END IF;
END;</code>

To cancel an insert operation within a IF statement, you can use MySQL's SIGNAL statement:

<code class="language-sql">CREATE TRIGGER foo BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '由于出生日期为未来日期，因此不允许插入';
  END IF;
END;</code>

Alternatively, you can modify the data to violate the constraint, for example, setting the value of the birthdate column to NULL:

when the date of birth is in the future

<code class="language-sql">CREATE TRIGGER foo BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.birthdate > CURRENT_DATE()
  THEN
    SET NEW.birthdate = NULL;
  END IF;
END;</code>

Since the birthdate column is defined as NOT NULL, the insert operation will be automatically rejected.

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