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. Do I have to break this down to separate triggers, a before insert and an after insert? This is a mysql database.
DELIMITER // CREATE TRIGGER download_ins BEFORE INSERT ON download FOR EACH ROW begin declare vip varbinary(16); select `ADDRESS` from `download`; vip = inet_aton(`ADDRESS`); set NEW.ip_address = vip; declare vrefer int(25) unsigned; select id from `ip_lookup` where NEW.ip_address between ip_lookup.start and ip_lookup.end limit 1; if vrefer is not null then set NEW.refer = vrefer; else exit; end if; END; // DELIMITER ;
Thanks in advance