MySQL for the Internet of Things

Chapter 4 ■ Data transformation

By now we know that triggers can be powerful, and in this case we can include a similar set of code to
determine the state value from the sensor. In fact, it resembles the Python example. Let’s look at the code
inside the trigger to focus on how the interpretation is accomplished. The following shows the trigger code:

DELIMITER //
CREATE TRIGGER set_pond_state BEFORE INSERT ON pond FOR EACH ROW
BEGIN
IF NEW.water_level > 1500 THEN
SET NEW.state = 'CLEAN';
ELSEIF NEW.water_level > 1150 THEN
SET NEW.state = 'LOW';
ELSEIF NEW.water_level > 700 THEN
SET NEW.state = 'NORMAL';
ELSE
SET NEW.state = 'HIGH';
END IF;
END //
DELIMITER ;

Now let’s test the trigger using a bulk insert. Here we will insert four new rows with values chosen
specifically to result in one of the four states.

INSERT INTO pond VALUES (null, 1501, null), (null, 1151, null), (null, 701, null), (null,
600, null);

There is an interesting system variable in MySQL. It is named last_insert_id and stores the last value
used for the auto-increment field. If you exercised the Python example, you will have already created the
table and inserted several rows. Rather than display (return) all of the rows, we can use the last_insert_id
to find the last value of the auto increment mechanism. In this case, the value is the last value used for
the last insert, but since this was a bulk insert, the value is the first value in the bulk (in this case 15). Try it
yourself.

mysql> select @@last_insert_id;
+------------------+
| @@last_insert_id |
+------------------+
| 15 |
+------------------+
1 row in set (0.00 sec)

The following shows the rows inserted:

mysql> SELECT * FROM pond WHERE id >= 15;
+----+-------------+--------+
| id | water_level | state |
+----+-------------+--------+
| 15 | 1501 | CLEAN |
| 16 | 1151 | LOW |
| 17 | 701 | NORMAL |
| 18 | 600 | HIGH |
+----+-------------+--------+
4 rows in set (0.01 sec)