Need help creating mysql trigger
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 monitor 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?