Trigger error in MySQL

Posted on

Question :

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *