SIGNAL SQLSTATE '45000' Doesn't stop Insert

Question

I am having problems implementing a trigger into my table. I am using MySQL, Phpmyadmin

Scenario: Table User(user_id, name, surname, date_of_joining), record(record_id, date_of_record, weight, height, id_user)

a user can have multiple records which show his weight and height at a certain date. id_user is a foreign key referencing to user_id.

I am trying to implement a trigger for insert and update which checks if date_of_record is greater than date_of_joining, if not, the insert should be stopped.

This is a trigger I tried and the insert still goes through

CREATE TRIGGER date_check_insert BEFORE INSERT ON record

FOR EACH ROW

BEGIN

    DECLARE date_of_registering DATE;

    SET date_of_registering = (SELECT date_of_registering FROM user WHERE user_id = new.id_user);

    IF (new.date_of_record NOT BETWEEN date_of_registering AND CURDATE()) THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Can not insert that value";

    END IF;

END

Any help is appreciated

Answer 1

I'd slightly change the trigger:

• Use session variable (so no DECLARE needed)
• Put comparison logic in a single place in the WHERE clause (easier to read & understand)
• Shows reason in error message
• Use SELECT INTO variable instead of SET variable = query (not necessary, I just prefer this way)

So:

CREATE TRIGGER date_check_insert BEFORE INSERT ON record
FOR EACH ROW
BEGIN
    SET @found = NULL;

    SELECT
        user_id INTO @found
    FROM
        user
    WHERE
            user_id = NEW.id_user
        AND NEW.date_of_record BETWEEN date_of_registering AND NOW() -- or CURDATE() depend on your datetype
    ;

    IF @found IS NULL THEN
        SET @msg = CONCAT('Can not insert. Out of range value ', NEW.date_of_record);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;
END