How can I get my student_before_update Trigger to display my error message if EnrolledDate is in future?


-- 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; 
Run code snippetExpand snippet


Hello Everyone,
I am working on creating a BEFORE UPDATE trigger which should display an error message if I UPDATE a student’s EnrolledDate with a future date. For example, the last inserted date in my Student record with an EnrolledDate is ‘2016-10-18’, so any other record after that date should flag an error message saying “Enrolled Date may not be in the future.” I have been literally working on this for hours, but I am not getting anyway. Can someone please help me? Here is my code so far.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.