Chapter 5 ■ MySQL priMerThis trigger will execute before each insert into the table. As you can see in the compound statement
(BEGIN...END), we set a column called sensor_level to LOW, OK, or HIGH depending on the value of the
sensor_value. To see this in action, consider the following command. The FOR EACH ROW syntax allows the
trigger to act on all rows in the transaction.
INSERT INTO plants (plant_name, sensor_value) VALUES ('plant1', 0.5544);
Since the value we supplied is less than the middle value (0.70), we expect the trigger to fill in the
sensor_level column for us. The following shows this indeed is what happened when the trigger fired:
+-------------+--------------+---------------------+--------------+
| plant_name | sensor_value | sensor_event | sensor_level |
+-------------+--------------+---------------------+--------------+
| plant1 | 0.5544 | 2015-09-23 20:00:15 | OK |
+-------------+--------------+---------------------+--------------+
1 row in set (0.00 sec)
This demonstrates an interesting and powerful way you can create derived columns with the power of
the database server and save the processing power of your sensor or aggregator nodes. I encourage you to
consider this and similar powerful concepts for leveraging the power of the database server.
Simple Joins
One of the most powerful concepts of database systems is the ability to make relationships (hence the name
relational) among the data. That is, data in one table can reference data in another (or several tables). The
most simplistic form of this is called a master-detail relationship where a row in one table references or is
related to one or more rows in another.
A common (and classic) example of a master-detail relationship is from an order-tracking system
where we have one table containing the data for an order and another table containing the line items for the
order. Thus, we store the order information such as customer number and shipping information once and
combine or “join” the tables when we retrieve the order proper.
Let’s look at an example from the sample database named world. You can find this database on the
MySQL web site (http://dev.mysql.com/doc/index-other.html). Feel free to download it and any other
sample database. They all demonstrate various designs of database systems. You will also find it handy to
practice querying the data as it contains more than a few, simple rows.
■Note if you want to run the following examples, you need to install the world database as described in
the documentation for the example (http://dev.mysql.com/doc/world-setup/en/world-setup-
installation.html).
Listing 5-7 shows an example of a simple join. There is a lot going on here, so take a moment to examine
the parts of the SELECT statement, especially how I specified the JOIN clause. You can ignore the LIMIT
option because that simply limits the number of rows in the result set.