I need help with "before insert" Trigger

[larry29936]

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

[urtrivedi]

Dear larry
You need not to have two triggers. Only before insert trigger is fine.

[AndrisP]

Don't need declare - select values direct to fields

DELIMITER //
CREATE TRIGGER download_ins  BEFORE INSERT ON download 
    FOR EACH ROW begin
    set new.ip_address = inet_aton(new.`ADDRESS`);
    set new.vrefer=(select id
            from `ip_lookup`
            where inet_aton(new.`ADDRESS`) between ip_lookup.start and ip_lookup.end
            limit 1
        );
END; //
DELIMITER ;

[larry29936]

@AndrisP, I get an error when I try to create the trigger on my web host using phpMyadmin. It errors at:

 set new.vrefer=(select id
            from `ip_lookup`
            where inet_aton(new.`ADDRESS`) between ip_lookup.start and ip_lookup.end
            limit 1
        );

The create trigger worked fine on my local server when I used the mysql shell to create it but I don't have ssh access to my web host.

[AndrisP]

You can insert all these values witout trigger e.g. use prepared statement:

insert into download (
    `ADDRESS`
    ,`ip_address`
    ,`vrefer`
)
select
    ?
    ,inet_aton(?)
    ,(select id from `ip_lookup` where inet_aton(?) between ip_lookup.start and ip_lookup.end limit 1)
;

and execute with array 3 times pass IP

$stmt->execute(['127.0.0.1','127.0.0.1','127.0.0.1']);

or pure SQL version:

set @ip='127.0.0.1';
insert into download (
    `ADDRESS`
    ,`ip_address`
    ,`vrefer`
)
select
    @ip
    ,inet_aton(@ip)
    ,(select id from `ip_lookup` where inet_aton(@ip) between ip_lookup.start and ip_lookup.end limit 1)
;

[AndrisP]

a small correction to my post

    $ip = filter_input(INPUT_SERVER,'REMOTE_ADDR',FILTER_VALIDATE_IP);
    $stmt->execute([$ip,$ip,$ip]);

[larry29936]

@AndrisP, thank you. That took care of it. I appreciate your help.