Day: May 16, 2020

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