troubleshooting Question

MySQL 5.6 trigger

Avatar of Jim Youmans
Jim YoumansFlag for United States of America asked on
* triggerMySQL Server
2 Comments1 Solution13 ViewsLast Modified:
I have a table that captures a bunch of information whenever a user logs into my app.  Three of the main fields are username, ip, and datetime.  I created a new table (user_ip_table) which has the following columns.

ID (unique auto gen)
username
ip
datetime

I want to create a trigger on the main table to keep track of the last ip address used by a given user.  So when a new row is inserted into the main table the trigger will fire and do one of the following.

if username/ip combo is present in  user_ip_table then do nothing
if username is present but with a different ip then update the ip address and datetime
if username is not present then insert it with ip and datetime

Is there an easy way to do this?  Or do I need to create 3 triggers, one for each case?

Thanks!

Jim
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros