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