MySQL Forums
Forum List  »  Triggers

Need help creating mysql trigger
Posted by: Larry Hale
Date: May 03, 2020 07:42AM

I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure:

`ID` int(5) NOT NULL autoincrement,
`LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(),
`IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL,
`FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql monitor using the following code:

Update access t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city
WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND t2.FILENAME is not null and t2.country is null;


How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download?

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help creating mysql trigger
1113
May 03, 2020 07:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.