MySQL Trigger Error – How to Resolve

MySQLtrigger

I'm using Toad for MySQL, and I have to make a trigger so that:

  • When the rent of a car is <=15 and >= 25, I insert a letter "A" in the field category
  • When the rent is > 25 and <= 35, insert the letter "B"
  • When the rent is >35 and <= 50, insert the letter "C"

Here is my table:

CREATE TABLE `automoviles` (
  `id_automoviles` int(11) NOT NULL AUTO_INCREMENT,
  `tipo_automovil` varchar(25) NOT NULL,
  `rentamiento` decimal(5,2) NOT NULL,
  `marca` varchar(25) DEFAULT NULL,
  `año` int(11) NOT NULL,
  `estado` varchar(10) DEFAULT NULL,
  `categoria` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id_automoviles`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I have the trigger this way, but it gives me an error:

DELIMITER $$
CREATE TRIGGER categoria AFTER INSERT on automoviles 
  FOR EACH ROW
  BEGIN
    IF (old.rentemiento >=15 and old.rentamiento <= 25)
    THEN
    UPDATE autoviles 
       set new.categoria = 'a'

    or if(old.rentamiento >=25 and old.rentamiento <=35)
    THEN 
    UPDATE automoviles set new.categoria = "b"

    else if (olD.rentamiennto >=35 and old.rentamiento <=50)
    THEN UPDATE automoviles set new.categoria = "c"

and here is the error!

  ERROR! There is no OLD row in on INSERT trigger

Best Answer

Welcome to the site, very nice first post that includes table ddl, well done!

As indicated in comment, OLD exists only in update and delete triggers. Furthermore, there is a spelling error on rentamiento (at least it is spelled differently in your table).

For changes of the row under insertion, typically a BEFORE trigger is used. You can use something like:

DELIMITER $$
CREATE TRIGGER categoria
BEFORE INSERT on automoviles
FOR EACH ROW
    SET new.categoria = CASE WHEN new.rentamiento BETWEEN 15 AND 25 THEN 'a'
                             WHEN new.rentamiento BETWEEN 26 AND 35 THEN 'b'
                             WHEN new.rentamiento BETWEEN 36 AND 50 THEN 'c'
                             ELSE 'd'
                        END;
$$
DELIMITER ;

I added a fallthrough case, which may or may not be necessary.

Related Question