larry29936 Posted May 3, 2020 Share Posted May 3, 2020 (edited) I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure: `ID` int(5) NOT NULL autoincrement, `LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(), `IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL, `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci; When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql shell using the following code: UPDATE access t2, ip_lookup t1 SET t2.country = t1.country, t2.area = t1.area, t2.city = t1.city WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND t2.FILENAME is not null and t2.country is null; How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download? Thanks in advance, Larry Edited May 3, 2020 by larry29936 Quote Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.