1

I have just started using triggers and one thing that I would need to do is to create a trigger that updates the same table that's inserting the row.

TABLE:

CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned DEFAULT '0',
  `country` varchar(150) DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6009 DEFAULT CHARSET=utf8;

Trigger:

DELIMITER $$
CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
BEGIN

IF NEW.country_id = 0

THEN
    UPDATE city
    INNER JOIN country B ON city.country = B.country
    SET NEW.country_id = B.country_id;

END IF;

END $$
DELIMITER ;

MySQL is just returning: Can't update table 'city' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there any way I can make this work?

Thanks everyone!

  • Trigger can NOT alter the table which altering fires this thigger. No exclusions. – Akina Apr 15 at 5:32
  • It is "wrong" to have both country and country_id in the city table. That redundant with the purpose of the country table. – Rick James Apr 23 at 22:57
0
CREATE TRIGGER `populate_country_id` BEFORE INSERT ON `city`
FOR EACH ROW
SET NEW.country_id = (SELECT CASE WHEN NEW.country_id = 0
                                  THEN country_id 
                                  ELSE NEW.country_id END
                      FROM country 
                      WHERE country = NEW.country 
                      LIMIT 1); -- as a precaution

BEGIN-END and DELIMITER reassign are excess.

PS. If NEW.country value is absent in country table then ... anycase inserted data is wrong.

  • Perfect! Thank you very much Akina! This works perfectly! Still getting my head around these triggers! Once again thank you! – Gilly Apr 15 at 21:00

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.