CODEWITHSUNDEEP
mysqlsqltriggersddl
Sajal Ali
Sajal Ali

Reputation: 437

how to use trigger for restricting modification

I have used this trigger to restrict the insertion of those records for which the date of birth is less then 18. This is working fine for the new insertion, but I also want to restrict the modification of the date of birth if it is set to less then 18. Please tell how can I do this?

DELIMITER $$
CREATE TRIGGER `test_candidate_before_insert` BEFORE INSERT ON `candidate` FOR EACH ROW
BEGIN
    IF 
        DATEDIFF(CURDATE(), NEW.date_of_birth)/365 < 18     
    THEN
        SIGNAL SQLSTATE '12345';
    END IF;
END$$   
DELIMITER ;

Upvotes: 1

Views: 86

Answers (2)

Mureinik
Mureinik

Reputation: 312404

MySQL, unfortunately, can't create trigger "before insert or update" like other common RDBMSs.

Just create another trigger for updating:

DELIMITER $$
CREATE TRIGGER `test_candidate_before_update` BEFORE UPDATE ON `candidate` FOR EACH ROW
BEGIN
    IF 
        DATEDIFF(CURDATE(), NEW.date_of_birth)/365 < 18     
    THEN
        SIGNAL SQLSTATE '12345';
    END IF;
END$$   
DELIMITER ;

Upvotes: 1

Greg Morris
Greg Morris

Reputation: 189

You need to define a separate UPDATE trigger.

Upvotes: 1

Related Questions