Databases Reference
In-Depth Information
Triggers
Triggers let you execute code when there's an
INSERT
,
UPDATE
, or
DELETE
statement. You
can direct MySQL to activate triggers before and/or after the triggering statement ex-
ecutes. They cannot return values, but they can read and/or change the data that the
triggering statement changes. Thus, you can use triggers to enforce constraints or busi-
ness logic that you'd otherwise need to write in client code.
Triggers can simplify application logic and improve performance, because they save
round-trips between the client and the server. They can also be helpful for automatically
updating denormalized and summary tables. For example, the Sakila sample database
uses them to maintain the
film_text
table.
MySQL's trigger implementation is very limited. If you're used to relying on triggers
extensively in another database product, you shouldn't assume they will work the same
way in MySQL. In particular:
• You can have only one trigger per table for each event (in other words, you can't
have two triggers that fire
AFTER INSERT
).
• MySQL supports only row-level triggers—that is, triggers always operate
FOR EACH
ROW
rather than for the statement as a whole. This is a much less efficient way to
process large datasets.
The following universal cautions about triggers apply in MySQL, too:
• They can obscure what your server is really doing, because a simple statement can
make the server perform a lot of “invisible” work. For example, if a trigger updates
a related table, it can double the number of rows a statement affects.
• Triggers can be hard to debug, and it's often difficult to analyze performance
bottlenecks when triggers are involved.
• Triggers can cause nonobvious deadlocks and lock waits. If a trigger fails the orig-
inal query will fail, and if you're not aware the trigger exists, it can be hard to
decipher the error code.
In terms of performance, the most severe limitation in MySQL's trigger implementation
is the
FOR EACH ROW
design. This sometimes makes it impractical to use triggers for
maintaining summary and cache tables, because they might be too slow. The main
reason to use triggers instead of a periodic bulk update is that they keep your data
consistent at all times.
Triggers also might not guarantee atomicity. For example, a trigger that updates a
MyISAM table cannot be rolled back if there's an error in the statement that fires it. It
is possible for a trigger to cause an error, too. Suppose you attach an
AFTER UPDATE
trigger to a MyISAM table and use it to update another MyISAM table. If the trigger
has an error that causes the second table's update to fail, the first table's update will
not be rolled back.
