<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 18. Triggers</title><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="stored-procedures.html" title="Chapter 17. Stored Procedures and Functions"><link rel="next" href="views.html" title="Chapter 19. Views"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 18. Triggers</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="stored-procedures.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="views.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="triggers"></a>Chapter 18. Triggers</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="triggers.html#create-trigger">18.1. <code class="literal">CREATE TRIGGER</code> Syntax</a></span></dt><dt><span class="section"><a href="triggers.html#drop-trigger">18.2. <code class="literal">DROP TRIGGER</code> Syntax</a></span></dt><dt><span class="section"><a href="triggers.html#using-triggers">18.3. Using Triggers</a></span></dt></dl></div><a class="indexterm" name="id3052644"></a><p>
Support for triggers is included beginning with MySQL 5.0.2. A
trigger is a named database object that is associated with a table
and that activates when a particular event occurs for the table. For
example, the following statements create a table and an
<code class="literal">INSERT</code> trigger. The trigger sums the values
inserted into one of the table's columns:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</code></strong>
mysql> <strong class="userinput"><code>CREATE TRIGGER ins_sum BEFORE INSERT ON account</code></strong>
-> <strong class="userinput"><code>FOR EACH ROW SET @sum = @sum + NEW.amount;</code></strong>
</pre><p>
This chapter describes the syntax for creating and dropping
triggers, and show some examples of how to use them. Discussion of
restrictions on use of triggers is given in
<a href="restrictions.html" title="Appendix I. Feature Restrictions">Appendix I, <i>Feature Restrictions</i></a>.
</p><p>
Binary logging for triggers is done as described in
<a href="stored-procedures.html#stored-procedure-logging" title="17.4. Binary Logging of Stored Routines and Triggers">Section 17.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="create-trigger"></a>18.1. <code class="literal">CREATE TRIGGER</code> Syntax</h2></div></div></div><pre class="programlisting">CREATE TRIGGER <em class="replaceable"><code>trigger_name</code></em> <em class="replaceable"><code>trigger_time</code></em> <em class="replaceable"><code>trigger_event</code></em>
ON <em class="replaceable"><code>tbl_name</code></em> FOR EACH ROW <em class="replaceable"><code>trigger_stmt</code></em>
</pre><p>
A trigger is a named database object that is associated with a
table, and that activates when a particular event occurs for the
table.
</p><p>
The trigger becomes associated with the table named
<em class="replaceable"><code>tbl_name</code></em>.
<em class="replaceable"><code>tbl_name</code></em> must refer to a permanent
table. You cannot associate a trigger with a
<code class="literal">TEMPORARY</code> table or a view.
</p><p>
<em class="replaceable"><code>trigger_time</code></em> is the trigger action
time. It can be <code class="literal">BEFORE</code> or
<code class="literal">AFTER</code> to indicate that the trigger activates
before or after the statement that activated it.
</p><p>
<em class="replaceable"><code>trigger_event</code></em> indicates the kind of
statement that activates the trigger. The
<em class="replaceable"><code>trigger_event</code></em> can be one of the
following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">INSERT</code>: The trigger is activated whenever a
new row is inserted into the table, for example through
<code class="literal">INSERT</code>, <code class="literal">LOAD DATA</code>, and
<code class="literal">REPLACE</code> statements.
</p></li><li><p>
<code class="literal">UPDATE</code>: The trigger is activated whenever a
row is modified, for example through <code class="literal">UPDATE</code>
statements.
</p></li><li><p>
<code class="literal">DELETE</code>: The trigger is activated whenever a
row is deleted from the table, for example through
<code class="literal">DELETE</code> and <code class="literal">REPLACE</code>
statements.
</p></li></ul></div><p>
It is important to note that the
<em class="replaceable"><code>trigger_event</code></em> does not so much
represent the SQL statement that activates the trigger as a table
operation. For example, a <code class="literal">BEFORE</code> trigger for
<code class="literal">INSERT</code> would be activated by not only
<code class="literal">INSERT</code> statements but also <code class="literal">LOAD
DATA</code> statements.
</p><p>
A potentially confusing example of this is the <code class="literal">INSERT
INTO .. ON DUPLICATE UPDATE ...</code> syntax: a
<code class="literal">BEFORE INSERT</code> trigger will activate for every
row, followed by either an <code class="literal">AFTER INSERT</code> trigger
or both the <code class="literal">BEFORE UPDATE</code> and <code class="literal">AFTER
UPDATE</code> triggers, depending on whether there was a
duplicate key for the row.
</p><p>
There cannot be two triggers for a given table that have the same
trigger action time and event. For example, you cannot have two
<code class="literal">BEFORE UPDATE</code> triggers for a table. But you can
have a <code class="literal">BEFORE UPDATE</code> and a <code class="literal">BEFORE
INSERT</code> trigger, or a <code class="literal">BEFORE UPDATE</code>
and an <code class="literal">AFTER UPDATE</code> trigger.
</p><p>
<em class="replaceable"><code>trigger_stmt</code></em> is the statement to
execute when the trigger activates. If you want to execute
multiple statements, use the <code class="literal">BEGIN ... END</code>
compound statement construct. This also enables you to use the
same statements that are allowable within stored routines. See
<a href="stored-procedures.html#begin-end" title="17.2.7. BEGIN ... END Compound Statement">Section 17.2.7, “<code class="literal">BEGIN ... END</code> Compound Statement”</a>.
</p><p>
<span class="bold"><strong>Note</strong></span>: Before MySQL 5.0.10,
triggers may not contain direct references to tables by name.
Beginning with MySQL 5.0.10, you may write triggers such as the
one named <code class="literal">testref</code> shown in this example:
</p><pre class="programlisting">CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|
DELIMITER ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
</pre><p>
If you insert the following values into table
<code class="literal">test1</code> as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO test1 VALUES </code></strong>
-> <strong class="userinput"><code>(1), (3), (1), (7), (1), (8), (4), (4);</code></strong>
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
</pre><p>
Then the data in the four tables will be as follows:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM test1;</code></strong>
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM test2;</code></strong>
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM test3;</code></strong>
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM test4;</code></strong>
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
</pre><p>
You can refer to columns in the table associated with the trigger
by using the aliases <code class="literal">OLD</code> and
<code class="literal">NEW</code>.
<code class="literal">OLD.<em class="replaceable"><code>col_name</code></em></code> refers
to a column of a an existing row before it is updated or deleted.
<code class="literal">NEW.<em class="replaceable"><code>col_name</code></em></code> refers
to the column of a new row to be inserted or an existing row after
it is updated.
</p><p>
When a trigger is activated, you need <code class="literal">SELECT</code>
privileges for all <code class="literal">OLD</code> and
<code class="literal">NEW</code> columns that the trigger references, and
you need <code class="literal">UPDATE</code> privileges for all
<code class="literal">NEW</code> columns that are targets of
<code class="literal">SET</code> assignments.
</p><p>
<span class="bold"><strong>Note</strong></span>: Currently, triggers are not
activated by cascaded foreign key actions. This limitation will be
lifted as soon as possible.
</p><p>
The <code class="literal">CREATE TRIGGER</code> statement requires the
<code class="literal">SUPER</code> privilege. It was added in MySQL 5.0.2.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="drop-trigger"></a>18.2. <code class="literal">DROP TRIGGER</code> Syntax</h2></div></div></div><pre class="programlisting">DROP TRIGGER [<em class="replaceable"><code>schema_name</code></em>.]<em class="replaceable"><code>trigger_name</code></em>
</pre><p>
Drops a trigger. The schema name is optional. If the schema is
omitted, the trigger is dropped from the current schema.
</p><p>
Prior to MySQL 5.0.10, the table name was required instead of the
schema name
(<code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>trigger_name</code></em></code>).
</p><p>
<span class="emphasis"><em>Note:</em></span> When upgrading from a previous version
of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers
<span class="emphasis"><em>before upgrading</em></span> and re-create them
afterwards, or else <code class="literal">DROP TRIGGER</code> does not work
after the upgrade. See <a href="installing.html#upgrading-from-4-1" title="2.10.2. Upgrading from Version 4.1 to 5.0">Section 2.10.2, “Upgrading from Version 4.1 to 5.0”</a> for a
suggested upgrade procedure.
</p><p>
The <code class="literal">DROP TRIGGER</code> statement requires the
<code class="literal">SUPER</code> privilege. It was added in MySQL 5.0.2.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="using-triggers"></a>18.3. Using Triggers</h2></div></div></div><p>
Support for triggers is included beginning with MySQL 5.0.2. This
section discusses how to use triggers and what limitations exist
regarding their use.
</p><p>
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.
</p><p>
A trigger is associated with a table and is defined to activate
when an <code class="literal">INSERT</code>, <code class="literal">DELETE</code>, or
<code class="literal">UPDATE</code> statement for the table executes. A
trigger can be set to activate either before or after the
triggering statement. For example, you can have a trigger activate
before each row that is deleted from a table or after each row
that is updated.
</p><p>
To create a trigger or drop a trigger, use the <code class="literal">CREATE
TRIGGER</code> or <code class="literal">DROP TRIGGER</code> statement.
The syntax for these statements is described in
<a href="triggers.html#create-trigger" title="18.1. CREATE TRIGGER Syntax">Section 18.1, “<code class="literal">CREATE TRIGGER</code> Syntax”</a> and
<a href="triggers.html#drop-trigger" title="18.2. DROP TRIGGER Syntax">Section 18.2, “<code class="literal">DROP TRIGGER</code> Syntax”</a>.
</p><p>
Here is a simple example that associates a trigger with a table
for <code class="literal">INSERT</code> statements. It acts as an
accumulator to sum the values inserted into one of the columns of
the table.
</p><p>
The following statements create a table and a trigger for it:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</code></strong>
mysql> <strong class="userinput"><code>CREATE TRIGGER ins_sum BEFORE INSERT ON account</code></strong>
-> <strong class="userinput"><code>FOR EACH ROW SET @sum = @sum + NEW.amount;</code></strong>
</pre><p>
The <code class="literal">CREATE TRIGGER</code> statement creates a trigger
named <code class="literal">ins_sum</code> that is associated with the
<code class="literal">account</code> table. It also includes clauses that
specify the trigger activation time, the triggering event, and
what to do with the trigger activates:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The keyword <code class="literal">BEFORE</code> indicates the trigger
action time. In this case, the trigger should activate before
each row inserted into the table. The other allowable keyword
here is <code class="literal">AFTER</code>.
</p></li><li><p>
The keyword <code class="literal">INSERT</code> indicates the event that
activates the trigger. In the example,
<code class="literal">INSERT</code> statements cause trigger activation.
You can also create triggers for <code class="literal">DELETE</code> and
<code class="literal">UPDATE</code> statements.
</p></li><li><p>
The statement following <code class="literal">FOR EACH ROW</code>
defines the statement to execute each time the trigger
activates, which occurs once for each row affected by the
triggering statement In the example, the triggered statement
is a simple <code class="literal">SET</code> that accumulates the values
inserted into the <code class="literal">amount</code> column. The
statement refers to the column as
<code class="literal">NEW.amount</code> which means “<span class="quote">the value of
the <code class="literal">amount</code> column to be inserted into the
new row.</span>”
</p></li></ul></div><p>
To use the trigger, set the accumulator variable to zero, execute
an <code class="literal">INSERT</code> statement, and then see what value
the variable has afterward:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @sum = 0;</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);</code></strong>
mysql> <strong class="userinput"><code>SELECT @sum AS 'Total amount inserted';</code></strong>
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
</pre><p>
In this case, the value of <code class="literal">@sum</code> after the
<code class="literal">INSERT</code> statement has executed is <code class="literal">14.98
+ 1937.50 - 100</code> or <code class="literal">1852.48</code>.
</p><p>
To destroy the trigger, use a <code class="literal">DROP TRIGGER</code>
statement. You must specify the schema name if the trigger is not
in the default schema:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DROP TRIGGER test.ins_sum;</code></strong>
</pre><p>
Triggers names exist in the schema namespace, meaning that all
triggers must have unique names within a schema. Triggers in
different schemas can have the same name.
</p><p>
In addition to the requirement that trigger names be unique for a
schema, there are other limitations on the types of triggers you
can create. In particular, you cannot have two triggers for a
table that have the same activate time and activation event. For
example, you cannot define two <code class="literal">BEFORE INSERT</code>
triggers or two <code class="literal">AFTER UPDATE</code> triggers for a
table. This should rarely be a significant limitation, because it
is possible to define a trigger that executes multiple statements
by using the <code class="literal">BEGIN ... END</code> compound statement
construct after <code class="literal">FOR EACH ROW</code>. (An example
appears later in this section.)
</p><p>
There are also limitations on what can appear in the statement
that the trigger executes when activated:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The trigger cannot invoke stored procedures that return data
to the client or use dynamic SQL by using the
<code class="literal">CALL</code> statement (stored procedures are
allowed to return data to the trigger through parameters).
</p></li><li><p>
The trigger cannot use statements that explicitly or
implicitly begin or end a transaction such as <code class="literal">START
TRANSACTION</code>, <code class="literal">COMMIT</code>, or
<code class="literal">ROLLBACK</code>.
</p></li><li><p>
Prior to MySQL 5.0.10, triggers may not contain direct
references to tables by name.
</p></li></ul></div><p>
The <code class="literal">OLD</code> and <code class="literal">NEW</code> keywords
enable you to access columns in the rows affected by a trigger.
(<code class="literal">OLD</code> and <code class="literal">NEW</code> are not case
sensitive.) In an <code class="literal">INSERT</code> trigger, only
<code class="literal">NEW.<em class="replaceable"><code>col_name</code></em></code> can be
used; there is no old row. In a <code class="literal">DELETE</code> trigger,
only <code class="literal">OLD.<em class="replaceable"><code>col_name</code></em></code>
can be used; there is no new row. In an <code class="literal">UPDATE</code>
trigger, you can use
<code class="literal">OLD.<em class="replaceable"><code>col_name</code></em></code> to
refer to the columns of a row before it is updated and
<code class="literal">NEW.<em class="replaceable"><code>col_name</code></em></code> to
refer to the columns of the row after it is updated.
</p><p>
A column named with <code class="literal">OLD</code> is read-only. You can
refer to it but not modify it. A column named with
<code class="literal">NEW</code> can be referred to if you have the
<code class="literal">SELECT</code> privilege for it. In a
<code class="literal">BEFORE</code> trigger, you can also change its value
with <code class="literal">SET NEW.<em class="replaceable"><code>col_name</code></em> =
<em class="replaceable"><code>value</code></em></code> if you have the
<code class="literal">UPDATE</code> privilege for it. This means you can use
a trigger to modify the values to be inserted into a new row or
that are used to update a row.
</p><p>
In a <code class="literal">BEFORE</code> trigger, the <code class="literal">NEW</code>
value for an <code class="literal">AUTO_INCREMENT</code> column is 0, not
the automatically generated sequence number that will be generated
when the new record actually is inserted.
</p><p>
<code class="literal">OLD</code> and <code class="literal">NEW</code> are MySQL
extensions to triggers.
</p><p>
By using the <code class="literal">BEGIN ... END</code> construct, you can
define a trigger that executes multiple statements. Within the
<code class="literal">BEGIN</code> block, you also can use other syntax that
is allowed within stored routines such as conditionals and loops.
However, just as for stored routines, when you define a trigger
that executes multiple statements, it becomes necessary to
redefine the statement delimiter if you are entering the trigger
with the <span><strong class="command">mysql</strong></span> program so that you can use the
‘<code class="literal">;</code>’ character within the trigger
definition. The following example illustrates these points. It
defines an <code class="literal">UPDATE</code> trigger that checks the new
value to be used for updating each row, and modifies the value to
be within the range from 0 to 100. This must be a
<code class="literal">BEFORE</code> trigger because the value needs to be
checked before it is used to update the row:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>delimiter //</code></strong>
mysql> <strong class="userinput"><code>CREATE TRIGGER upd_check BEFORE UPDATE ON account</code></strong>
-> <strong class="userinput"><code>FOR EACH ROW</code></strong>
-> <strong class="userinput"><code>BEGIN</code></strong>
-> <strong class="userinput"><code>IF NEW.amount < 0 THEN</code></strong>
-> <strong class="userinput"><code>SET NEW.amount = 0;</code></strong>
-> <strong class="userinput"><code>ELSEIF NEW.amount > 100 THEN</code></strong>
-> <strong class="userinput"><code>SET NEW.amount = 100;</code></strong>
-> <strong class="userinput"><code>END IF;</code></strong>
-> <strong class="userinput"><code>END;//</code></strong>
mysql> <strong class="userinput"><code>delimiter ;</code></strong>
</pre><p>
It can be easier to define a stored procedure separately and then
invoke it from the trigger using a simple <code class="literal">CALL</code>
statement. This is also advantageous if you want to invoke the
same routine from within several triggers.
</p><p>
MySQL handles errors during trigger execution as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If a <code class="literal">BEFORE</code> trigger fails, the operation on
the corresponding row is not performed.
</p></li><li><p>
An <code class="literal">AFTER</code> trigger is executed only if the
<code class="literal">BEFORE</code> trigger (if any) and the row
operation both execute successfully.
</p></li><li><p>
An error during either a <code class="literal">BEFORE</code> or
<code class="literal">AFTER</code> trigger results in failure of the
whole statement that caused trigger invocation.
</p></li><li><p>
For transactional tables, failure of a trigger (and thus the
whole statement) should cause rollback of all changes
performed by the statement. For non-transactional tables, such
rollback cannot be done, so although the statement fails, any
changes performed prior to the point of the error remain in
effect.
</p></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="stored-procedures.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="views.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 17. Stored Procedures and Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 19. Views</td></tr></table></div></body></html>
