Jump to content

Need help creating mysql trigger


Recommended Posts

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 shell 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?

Thanks in advance,

Larry

Edited by larry29936
Link to post
Share on other sites

Why do you feel this urge to duplicate the country, area,city data?

You only need it in the ip_lookup table. Join the two tables in a query whenever you want that info for access table records.

Link to post
Share on other sites

@Barand, This query gives an error of "Operand should contain 1 column(s)" in mysql shell:

SELECT (download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city)
FROM download, ip_lookup
WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;

 

That's why I wanted all data in one table.

Edited by larry29936
Link to post
Share on other sites

Remove the ( ) from the SELECT clause.

download
+----+---------------------+------------+----------+
| ID | LOG_TIME            | IP_ADDRESS | FILENAME |
+----+---------------------+------------+----------+
|  1 | 2020-05-03 17:26:56 |         20 | NULL     |
|  2 | 2020-05-03 17:26:56 |        160 | a.txt    |
|  3 | 2020-05-03 17:26:56 |        205 | b.txt    |
+----+---------------------+------------+----------+

ip_lookup
+-------+----------+--------+---------+--------+------------+
| IP_ID | start_ip | end_ip | country | area   | city       |
+-------+----------+--------+---------+--------+------------+
|     1 |        1 |    100 | USA     | NY     | New York   |
|     2 |      101 |    200 | UK      | N West | Manchester |
|     3 |      201 |    300 | Spain   | North  | Barcelona  |
+-------+----------+--------+---------+--------+------------+

mysql> SELECT download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city
    -> FROM download, ip_lookup
    -> WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;
+----------+---------+--------+------------+
| FILENAME | country | area   | city       |
+----------+---------+--------+------------+
| NULL     | USA     | NY     | New York   |
| a.txt    | UK      | N West | Manchester |
| b.txt    | Spain   | North  | Barcelona  |
+----------+---------+--------+------------+

 

Also, use explicit join syntax and not "FROM A,B WHERE ..."

SELECT download.FILENAME
     , ip_lookup.country
     , ip_lookup.area
     , ip_lookup.city
FROM download
     INNER JOIN
     ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip; 

 

Link to post
Share on other sites

@Barand, ran the following  in mysql shell and it took 7 minutes to run:

SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city 
FROM download 
INNER JOIN ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip 
WHERE download.FILENAME is not null;

There are 5 million + records in the ip_lookup table so that's probably the hang up. That was one reason why I wanted all the information in the download table.
 

Edited by larry29936
Link to post
Share on other sites

@Barand, here's what you asked for:


download	CREATE TABLE `download` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IP_ADDRESS` int(64) unsigned 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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci	

 


ip_lookup	CREATE TABLE `ip_lookup` (
  `id` int(25) unsigned NOT NULL AUTO_INCREMENT,
  `start_ip` int(64) unsigned NOT NULL,
  `end_ip` int(64) unsigned NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_mysql500_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_ip` (`start_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=5277086 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci	

 

Link to post
Share on other sites

As a compromise, when you add a download record, get the id of the matching ip_lookup record and store that in the download record.

That way you aren't duplicating the contry/area/city data and you get extremely efficient joins.

SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city 
FROM download 
INNER JOIN ip_lookup ON download.ip_id = ip_lookup.id
WHERE download.FILENAME is not null

 

Edited by Barand
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.