20.3 Using Triggers

A trigger is a named database object that is associated with a
table, and that activates when a particular event occurs for the
table. Some uses for triggers are to perform checks of values to be
inserted into a table or to perform calculations on values involved
in an update.

A trigger is defined to activate when a statement inserts, updates,
or deletes rows in the associated table. These row operations are
trigger events. For example, rows can be inserted by
INSERT or LOAD
DATA statements, and an insert trigger activates for each
inserted row. A trigger can be set to activate either before or
after the trigger event. For example, you can have a trigger
activate before each row that is inserted into a table or after each
row that is updated.

Important

MySQL triggers activate only for changes made to tables by SQL
statements. They do not activate for changes in views, nor by
changes to tables made by APIs that do not transmit SQL statements
to the MySQL Server. This means that:

Triggers are not activated by changes in
INFORMATION_SCHEMA or
performance_schema tables, because these
tables are actually views.

User Comments

A trigger can be used to do a real time pivot of an entity-attribute-value table. Suppose you have two tables, "eav" with columns entity, attribute, and value (entity+attribute are the primary key) and "pivot" with columns id,Author,Title,Publisher.Here's the code:

Be aware of that MySQL does foreign key checks BEFORE invoking any trigger. So it is not possible to implement a BEFORE INSERT trigger that enters up a missing column value with a foreign key constraint.