Member Avatar for 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

  • 3 Contributors
  • 6 Replies
  • 248 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by larry29936

Recommended Answers

All 6 Replies

Member Avatar for urtrivedi

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

Member Avatar for 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 ;
Member Avatar for 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.

Member Avatar for 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)
;
Edited by AndrisP
Member Avatar for AndrisP

a small correction to my post

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.