mysql - 如何防止我的学生表将来出现日期？

Question

-- Create a trigger that will prevent  an update to a student table if EnrolledDate is in the future

USE College ;

DROP TRIGGER IF EXISTS  Student_Before_Update; 

DELIMITER $$

        CREATE TRIGGER Student_Before_Update
        BEFORE UPDATE ON Student 
        FOR EACH ROW 
        
      BEGIN 
      
	IF NEW.EnrolledDate > '2016-10-18'  THEN 
				 SIGNAL SQLSTATE VALUE '45000'
				  SET MESSAGE_TEXT= 'Enrolled date may not be in the future';
		END IF; 

      END $$

DELIMITER ;

UPDATE Student
SET EnrolledDate= DATE(now())
WHERE ID= 1; 

UPDATE Student
SET EnrolledDate= DATE_ADD(now(),INTERVAL 1 DAY)
WHERE ID=1; 

大家好，

我正在创建一个 BEFORE UPDATE 触发器，它应该显示错误如果我用 future 的日期更新学生的注册日期，则会收到消息。例如，Student 表中最后插入的 EnrolledDate 是“2016-04-17”，因此该日期之后的任何其他记录都应标记一条错误消息，指出“注册日期可能不是将来的日期”。我实际上已经为此工作了几个小时，但无论如何我都没有得到。有人可以帮帮我吗？到目前为止，这是我的代码。

Answer 1

试试这个:

-- Create a trigger that will prevent  an update to a student table if EnrolledDate is in the future

USE College ;

DROP TRIGGER IF EXISTS  Student_Before_Update; 

DELIMITER $$

CREATE TRIGGER Student_Before_Update
BEFORE UPDATE ON Student 
FOR EACH ROW 

BEGIN 

    IF DATE_FORMAT(NEW.EnrolledDate,'%Y-%m-%d') > DATE_FORMAT(NOW(),'%Y-%m-%d')  THEN 
        SIGNAL SQLSTATE VALUE '45000'
        SET MESSAGE_TEXT= 'Enrolled date may not be in the future';
    END IF; 

END $$

DELIMITER ;

UPDATE Student
SET EnrolledDate= DATE(now())
WHERE ID= 1; 

UPDATE Student
SET EnrolledDate= DATE_ADD(now(),INTERVAL 1 DAY)
WHERE ID=1;