Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 316

Solution
Use a BEFORE INSERT trigger. This enables you to initialize a column to the value of an
arbitrary expression. In other words, the trigger performs dynamic column initializa‐
tion by calculating the default value.
Discussion
Other than TIMESTAMP and DATETIME columns, which can be initialized to the current
date and time (see Recipe 6.7 ), default column values in MySQL must be constants. You
cannot define a column with a DEFAULT clause that refers to a function call or other
arbitrary expression, and you cannot define one column in terms of the value assigned
to another column. That means each of these column definitions is illegal:
d DATE DEFAULT NOW()
i INT DEFAULT ( ... some subquery ... )
hash_val CHAR(32) DEFAULT MD5(blob_col)
You can work around this limitation by setting up a suitable trigger, which enables you
to initialize a column however you want. In effect, the trigger implements a dynamic
(or calculated) default column value.
The appropriate type of trigger for this is BEFORE INSERT , which enables column values
to be set before they are inserted into the table. (An AFTER INSERT trigger can examine
column values for a new row, but by the time the trigger activates, it's too late to change
the values.)
To see how this works, recall the scenario in Recipe 9.2 that created a sales_tax_rate()
lookup function to return a rate from the sales_tax_rate table given a customer state
of residence. Suppose that you anticipate a need to know at some later date the tax rate
from the time of sale. It's not necessarily true that at that later date you could look up
the value from the sales_tax_rate table; rates change and the rate in effect then might
differ. To handle this, store the rate with the purchase invoice, initializing it automati‐
cally using a trigger.
A cust_invoice table for storing sales information might look like this:
CREATE TABLE cust_invoice
(
id INT NOT NULL AUTO_INCREMENT ,
state CHAR ( 2 ), # customer state of residence
amount DECIMAL ( 10 , 2 ), # sale amount
tax_rate DECIMAL ( 3 , 2 ), # sales tax rate at time of purchase
... other columns ...
PRIMARY KEY ( id )
);
To initialize the sales tax column for inserts into the cust_invoice table, use a BEFORE
INSERT trigger that looks up the rate and stores it in the table: