I've just started using triggers and one thing to do is to create a trigger that updates the same array as the one inserting the line.
CREATE TABLE `city` ( `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 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 TO` city` FOR EACH ROW TO START 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; FIN $$ DELIMITER;
MySQL is just returning: Can not update the table & # 39; city & # 39; in the function / trigger saved, because it is already used by the instruction that called this function / this trigger stored.
Is there a way I can do this job?
Thank you everyone!