Create Trigger to convert IP addresses

landship
landship used Ask the Experts™
on
I want to convert IP addresses to Number using a trigger (unless someone has a better idea). The code below works if an IP address is entered, but if it is already converted to a number it tries to insert NULL.

I figure there is some way to add an if statement, but it is beyond my know-how
create trigger bu_user before update on user for each row
set new.user_ip = INET_ATON(new.user_ip);
 
- and -
 
create trigger bi_user before insert on user for each row
set new.user_ip = INET_ATON(new.user_ip);

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This kind of conversion is supposed to be the job of the front-end interface. Your website or application should be making sure the correct data gets inserted into the database.

Easiest thing to do is this:

set new.user_ip = IFNULL(INET_ATON(new.user_ip), new.user_ip);

It should use "INET_ATON(new.user_ip)", unless that expression is null, in which case it will default to just "new.user_ip".

Author

Commented:
Thanks Frosty555, that works perfectly. I plan to process it on the front-end, but was worried if I miss it on the front-end it would only store the first group of numbers and not flag any errors.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial