I just started using the triggers and one thing I would have to do is create an activator that updates the same table that is inserting the row.
CREATE TABLE & # 39; city & # 39; ( `city_id` smallint (5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar (50) NOT NULL, `country_id` smallint (5) unsigned DEFAULT & # 39; 0 & # 39 ;, `country` varchar (150) DEFAULT NULL, `last_update` timestamp NO NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (` country_id`), CONSTRAINT `city_ibfk_1` STRANGE KEY (` country_id`) REFERENCES `country` (` country_id`) IN CASCADE UPDATE ) MOTOR = InnoDB AUTO_INCREMENT = 6009 DEFAULT CHARSET = utf8;
DELIMITER $$ CREATE THE TRIGGER `populate_country_id` BEFORE INSERTING IN` city` FOR EACH ROW START IF NEW.country_id = 0 SO UPDATE city INNER JOIN country B ON city.country = B.country SET NEW.country_id = B.country_id; IT WILL END YES; END OF PAYMENT Delimiter;
MySQL has just returned: Can not update the table & # 39; city & # 39; in the stored function / trigger because it is already used by the instruction that invoked this stored function / trigger.
Is there any way to make this work?
Thank you all!