MySQL trigger not functioning correctly

I currently working my AWS RDS MySQL through R notebook.

I have a table like this:

create table t (
  tid INTEGER NOT NULL,
  fruit ENUM('A', 'B', 'C') NOT NULL,
  PRIMARY KEY(tid)
);

With a trigger like this

DELIMITER \\

CREATE
TRIGGER fruit_on_insert     
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
  IF NEW.fruit LIKE "%apple%" THEN
    SET NEW.fruit = 'A';
  ELSEIF NEW.fruit LIKE "%banana%" THEN
    SET NEW.fruit = 'B';
  ELSE
    SET NEW.fruit = 'C';
  END IF;
END

I could create table and trigger without problems; however, when I insert something like this

INSERT INTO t (tid, fruit)
VALUES (1, 'apple tree');

The insertion was successful but I would get an instance of fruit with 'C', instead of 'A'

Wonder if anyone has some insights, thank!

Solution

The ENUM is being checked even before the trigger is fired. So because the value you're trying to insert doesn't exist in the enum, the insert is failing.

You can fix this by removing the enum and replacing it with a varchar. You might be able to manage this with a CHECK constraint, but you need to be on MySQL 8.0.16 or greater.

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  tid INTEGER NOT NULL,
  fruit VARCHAR(100) NOT NULL,
  PRIMARY KEY(tid)
  );
  
DELIMITER //

DROP TRIGGER IF EXISTS fruit_on_insert //

CREATE
TRIGGER fruit_on_insert     
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
  IF NEW.fruit LIKE "%apple%" THEN SET NEW.fruit = 'A';
  ELSEIF NEW.fruit LIKE "%banana%" THEN
    SET NEW.fruit = 'B';
  ELSE
    SET NEW.fruit = 'C';
  END IF;
END //

DELIMITER ;

INSERT INTO t (tid, fruit) VALUES (1, 'apple tree');
INSERT INTO t (tid, fruit) VALUES (2, 'Banana blossom');
INSERT INTO t (tid, fruit) VALUES (3, 'My mate is a farmer and has an apple orchard');
INSERT INTO t (tid, fruit) VALUES (4, 'Big Billy'' Big Banana Bonanza!');
INSERT INTO t (tid, fruit) VALUES (5, 'The quick brown fox jumps over the lazy dog');

SELECT *
FROM t;