Why is mySQL Trigger not saving?

user_9083589 0
Viewed 322

I'm trying to create a trigger that stops insert if two inserts with same "St_Obr" were already made. It shows no errors, yet when I execute SQL script the trigger doesn't get saved. At least it's not shown when I execute SHOW TRIGGERS;

Code:

DELIMITER //

CREATE TRIGGER ob_limit
BEFORE INSERT ON TUP.Lab FOR EACH ROW
BEGIN
    SELECT @a = COUNT(*)
    FROM TUP.Lab
    WHERE St_Obr = NEW.St_Obr;
    IF (@a > 2) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'MAX 2!';
    END IF;
END //
DELIMITER ;
sql-insert count triggers mysql sql
2 Answers

You want to assign the count to the variable, while your code compares it. This would require using a declared variable and selecting into it.

But I find that it is simpler to skip the variable and run the query directly in the if statement:

CREATE TRIGGER ob_limit
BEFORE INSERT ON Lab FOR EACH ROW
BEGIN
    
    IF ((SELECT COUNT(*) FROM Lab WHERE St_Obr = NEW.St_Obr) >= 2) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'MAX 2!';
    END IF;

END //

DELIMITER ;

Note that I change the inequality from > to >=2: the former allows three rows per St_Obr, while the latter permits 2 only (which is what you seem to want).

Here is a Demo on DB Fiddle; you can comment or uncomment the thrid insert to generate the error.

user_106767160

In an BEFORE trigger the inserted row cannot be "seen" by the SELECT count(*) ... statement in the trigger. It is execute before the new row is inserted into the table. You can reduce the limit to 1 or use >= as GMB suggests. Or you can change the trigger to an AFTER trigger.

CREATE TRIGGER ob_limit
               AFTER INSERT ON Lab
               FOR EACH ROW
BEGIN
 IF (SELECT count(*)
            FROM Lab
            WHERE st_obr = new.st_obr) > 2 THEN
    SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'MAX 2!';
  END IF;
END //
DELIMITER ;

(Note that the way you assigned the result to the variable was also erroneous. But there's no need for a variable, you can use the subquery directly in the comparison.)

user_96614240
Related