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.
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;
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?