Recipe 16.4. Using a Trigger to Define Dynamic Default Column Values

Problem

A column in a table needs to be initialized to a nonconstant value, but MySQL allows only constant default values.

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 initialization by calculating the default value.

Discussion

Other than TIMESTAMP columns, which can be initialized to the current date and time, the default value for a column in MySQL must be a constant value. 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 ...) hashval CHAR(32) DEFAULT MD5(blob_col)

However, 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 enables you to define dynamic (or calculated) default column values.

The appropriate type of trigger for this is BEFORE INSERT, because that enables you to set column values 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.)

Suppose that you want to use a table for storing large data values such as PDF or XML documents, images, or sounds, but you also want to be able to look them up quickly later. A TEXT or BLOB data type might be suitable for storing the values, but is not very suitable for finding them. (Comparisons in a lookup operation will be slow for large values.) To work around this problem, use the following strategy:

Compute some kind of hash value for each data value and store it in the table along with the data.
To look up the row containing a particular data value, compute the hash value for the value and search the table for that hash value. For best performance, make sure that the hash column is indexed.

To implement this strategy, a BEFORE INSERT trigger is helpful because you can have the trigger compute the hash value to be stored in the table for new data values. (If you might change the data value later, you should also set up a BEFORE UPDATE trigger to recompute the hash value.)

The following example assumes that you want to store documents in a table, along with the document author and title. In addition, the table contains a column for storing the hash value computed from the document contents. To generate hash values, the example uses the MD5⁠(⁠ ⁠ ⁠) function, which returns a 32-byte string of hexadecimal characters. That's actually still somewhat long to use for a comparison value. Nevertheless, it's a lot shorter than full-column comparisons based on contents of very long documents.

First, create a table to hold the document information and the hash values calculated from the document contents. The following table uses a MEDIUMBLOB column to allow storage of documents up to 16 MB in size:

CREATE TABLE doc_table (   author   VARCHAR(100) NOT NULL,   title    VARCHAR(100) NOT NULL,   document MEDIUMBLOB NOT NULL,   doc_hash CHAR(32) NOT NULL,   PRIMARY KEY (doc_hash) );

Next, to handle inserts, create a BEFORE INSERT trigger that uses the document to be inserted to calculate the hash value and causes that value to be stored in the table:

CREATE TRIGGER bi_doc_table BEFORE INSERT ON doc_table FOR EACH ROW SET NEW.doc_hash = MD5(NEW.document);

This trigger is simple and its body contains only a single SQL statement. For a trigger body that needs to execute multiple statements, use BEGIN ... END compound-statement syntax. In that case, if you use mysql to create the event, you'll need to change the statement delimiter while you're defining the trigger, as discussed in Section 16.1.

Within the trigger, NEW. col_name refers to the new value to be inserted into the given column. By assigning a value to NEW. col_name within the trigger, you cause the column to have that value in the new row.

Finally, insert a row and check whether the trigger correctly initializes the hash value for the document:

mysql> INSERT INTO doc_table (author,title,document)     -> VALUES('Mr. Famous Writer','My Life as a Writer',     ->        'This is the document'); mysql> SELECT * FROM doc_table\G; *************************** 1. row ***************************   author: Mr. Famous Writer    title: My Life as a Writer document: This is the document doc_hash: 5282317909724f9f1e65318be129539c mysql> SELECT MD5('This is the document'); +----------------------------------+ | MD5('This is the document')      | +----------------------------------+ | 5282317909724f9f1e65318be129539c | +----------------------------------+

The first SELECT shows that the doc_hash column was initialized even though the INSERT provided no value for it. The second SELECT shows that the hash value stored in the row by the trigger is correct.

The example thus far demonstrates how a trigger enables you to initialize a row column in a way that goes beyond what is possible with the DEFAULT clause in the column's definition. The same idea applies to updates, and it's a good idea to apply it in the present scenario: when initialization of a column is a function of the value in another column (as is the case for doc_hash), it is dependent on that column. Therefore, you should also update it whenever the column on which it depends is updated. For example, if you update a value in the document column, you should also update the corresponding doc_hash value. This too can be handled by a trigger. Create a BEFORE UPDATE TRigger that does the same thing as the INSERT trigger:

CREATE TRIGGER bu_doc_table BEFORE UPDATE ON doc_table FOR EACH ROW SET NEW.doc_hash = MD5(NEW.document);

Test the UPDATE trigger by updating the document value and checking whether the hash value is updated properly:

mysql> UPDATE doc_table SET document = 'A new document'     -> WHERE document = 'This is the document'; mysql> SELECT * FROM doc_table\G; *************************** 1. row ***************************   author: Mr. Famous Writer    title: My Life as a Writer document: A new document doc_hash: 21c03f63d2f01b598665d4d960f3a4f2 mysql> SELECT MD5('A new document'); +----------------------------------+ | MD5('A new document')            | +----------------------------------+ | 21c03f63d2f01b598665d4d960f3a4f2 | +----------------------------------+