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