1

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
  • 1
    Hi, Jimmy. Questions here are required to be in English, so I translated it for you. Feel free to fix things I screwed up. Can you include the error you're getting? – Brent Ozar Jan 4 at 21:52
  • Sorry I did not know about that. – jimmy marroquin Jan 4 at 21:59
  • ERROR! There is no OLD row in on INSERT trigger – jimmy marroquin Jan 4 at 21:59
  • Well, the error message seems pretty self-explanatory: there was no row before you inserted it, so old is meaningless. Also, generally it's not very wise to store something in the table that can be easily computed when querying the table, which seems to be the case here. – mustaccio Jan 4 at 22:46
0

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.

  • Thanks for your help!!!!! and thank for the information about update and delete trigger – jimmy marroquin Jan 4 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.