Chapter 4 ■ Data transformationThe first thing you should consider is whether to generate the new data on the fly or as a column stored
in the table. Generating the new value on the fly will mean less storage used but may come as a premium
when retrieving the rows. That is, it may take longer to retrieve a large number of rows since the calculation
is deferred and done all at once. Thus, saving the new data in the table means less time spent retrieving the
rows but does require a bit more storage space. Fortunately, storage space isn’t usually the issue. Note that
the calculation specified in the SELECT statement is still executed on the database server albeit once for each
row as it is retrieved.
As to whether to generate the data in the database with a trigger or as part of the INSERT statement,
we should consider where we want the calculation to be executed. If placed in a trigger, the calculation is
performed on the database server. But if placed in the INSERT statement, the calculation is performed on the
client (the one sending the data). Thus, if you have complex calculations or limited processing power, you
may want to choose the trigger option.
Before we look at the database examples, the following shows the table needed for executing the
examples. I include this here so that you can test these examples yourself and so that you can see the only new
column added is the calibrated column, which is used by the trigger example. Listing 4-9 shows the test table.
Listing 4-9. Table Schema for Derived and Calculated Columns Example
CREATE TABLE derived_annotation_test (
id int(11) NOT NULL AUTO_INCREMENT,
blood_oxygen float NOT NULL,
blood_oxygen_corrected float DEFAULT NULL,
weight float DEFAULT NULL,
volts float DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
Notice we add a column for the calibration of the blood oxygen example but not for the other two
values. Those we will generate on the fly. Now let’s see how each of the three options can be implemented in
code.
Derived and Calculated Columns Using a Trigger
A trigger is a good way to program your database to populate a column. In this case, we want to write a
trigger that executes each time a row is added to the table and, when it does, calculate the calibrated value
using a simple formula. A trigger can be executed (or triggered) in several ways such as before or after an
insert or delete. A trigger is associated with a specific table, but a table may have more than one trigger.
We will discuss triggers in more detail in Chapter 5 , but for now observe the following:
CREATE TRIGGER calibrate_blood_oyxgen BEFORE INSERT ON derived_annotation_test
FOR EACH ROW SET NEW.blood_oxygen_corrected = NEW.blood_oxygen + 0.785;
What we see here is a trigger set to execute before an insert for the sample table named derived_
annotationtest. Notice the second line reads quite clearly that a new value will be set for the blood
oxygen_corrected column with the value of the blood_oxygen value plus 0.785.
Now let’s see what happens when we insert a row. The following inserts a row in the table. Notice I
specified a list of columns followed by a list of values. If you do not specify the column list, you must supply
values for all of the columns in the table.
INSERT INTO derived_annotation_test (blood_oxygen, weight, volts) VALUES (94.88, 46.07,
68.42);