I’m trying to create a trigger that does two things, first take an ip that’s in dot notation and run inet_aton on it and put the result in another field. Second, checks a lookup table to identify an ip range that the result of the first action falls into and enters the id of that row in the table. This is on a mysql database on my web host. Here’s what I’ve tried:
DELIMITER //
CREATE TRIGGER before_ins_download BEFORE INSERT ON download
FOR EACH ROW begin
set new.ip_address = inet_aton(new.`ADDRESS`),
new.refer=(select `id`
from `ip_lookup` as i
where new.ip_address between i.start and i.end
limit 1 );
END; //
DELIMITER ;
It works without the new.refer part but after I added that it just seems to hang and never gets to the closing DELIMITER; . All ip columns are indexed.
Does anyone see a mistake in my code? Thanks for looking.
EDIT: I was just too impatient. The “between” test has to run against 5 million records.