0

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 EnrolledDate in the Student table 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.

-- 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; 
2
Contributors
1
Reply
27
Views
5 Months
Discussion Span
Last Post by AndrisP
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.