Author: paul
Date: 2008-09-12 17:49:47 +0200 (Fri, 12 Sep 2008)
New Revision: 11795
Log:
r33950@frost: paul | 2008-09-12 10:49:38 -0500
Triggers: General revisions to eliminate redundancies
Modified:
trunk/refman-5.0/renamed-nodes.txt
trunk/refman-5.0/triggers.xml
trunk/refman-5.1/renamed-nodes.txt
trunk/refman-5.1/triggers.xml
trunk/refman-6.0/renamed-nodes.txt
trunk/refman-6.0/triggers.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:33944
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:33057
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:33950
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:33057
Modified: trunk/refman-5.0/renamed-nodes.txt
===================================================================
--- trunk/refman-5.0/renamed-nodes.txt 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-5.0/renamed-nodes.txt 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 555 bytes
@@ -740,6 +740,7 @@
upgrading-grant-tables mysql-fix-privilege-tables
using-mysql-programs programs-using 2009-01-01
using-mysqlcheck mysqlcheck
+using-triggers triggers 2009-01-12
utility-statements sql-syntax-utility 2009-07-14
variables user-variables
vb-types connector-odbc-usagenotes-apptips
Modified: trunk/refman-5.0/triggers.xml
===================================================================
--- trunk/refman-5.0/triggers.xml 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-5.0/triggers.xml 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 5, Lines Added: 247, Lines Deleted: 292; 24611 bytes
@@ -5,7 +5,7 @@
]>
<section id="triggers">
- <title>Triggers</title>
+ <title>Using Triggers</title>
<indexterm>
<primary>triggers</primary>
@@ -13,26 +13,22 @@
<para>
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 is activated when a particular event occurs for the table.
- For example, the following statements create a table and an
- <literal>INSERT</literal> trigger. The trigger sums the values
- inserted into one of the table's columns:
+ 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.
</para>
-<programlisting>
-<!--
-mysql> DROP TABLE IF EXISTS account;
-Query OK, 0 rows affected, 1 warning (0.01 sec)
--->
-mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
-Query OK, 0 rows affected (0.03 sec)
+ <para>
+ A trigger is defined to activate when an <literal>INSERT</literal>,
+ <literal>DELETE</literal>, or <literal>UPDATE</literal> statement
+ executes for the associated table. 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 inserted into a
+ table or after each row that is updated.
+ </para>
-mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
- -> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
-Query OK, 0 rows affected (0.06 sec)
-</programlisting>
-
<important>
<para>
MySQL triggers are activated by SQL statements
@@ -77,121 +73,84 @@
</itemizedlist>
- <section id="using-triggers">
+ <para>
+ To create a trigger or drop a trigger, use the <literal>CREATE
+ TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement. The
+ syntax for these statements is described in
+ <xref linkend="create-trigger"/>, and
+ <xref linkend="drop-trigger"/>.
+ </para>
- <title>Using Triggers</title>
+ <para>
+ Here is a simple example that associates a trigger with a table for
+ <literal>INSERT</literal> statements. The trigger acts as an
+ accumulator, summing the values inserted into one of the columns of
+ the table.
+ </para>
- <para>
- Support for triggers is included beginning with MySQL 5.0.2. This
- section discusses how to use triggers and some limitations
- regarding their use. Additional information about trigger
- limitations is given in <xref linkend="routine-restrictions"/>.
- </para>
-
- <para>
- 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.
- </para>
-
- <para>
- A trigger is associated with a table and is defined to activate
- when an <literal>INSERT</literal>, <literal>DELETE</literal>, or
- <literal>UPDATE</literal> 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.
- </para>
-
- <important>
- <para>
- MySQL triggers are activated by SQL statements
- <emphasis>only</emphasis>. They are not activated by changes in
- tables made by APIs that do not transmit SQL statements to the
- MySQL Server; in particular, they are not activated by updates
- made using the <literal>NDB</literal> API.
- </para>
- </important>
-
- <para>
- To create a trigger or drop a trigger, use the <literal>CREATE
- TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement.
- The syntax for these statements is described in
- <xref linkend="create-trigger"/>, and
- <xref linkend="drop-trigger"/>.
- </para>
-
- <para>
- Here is a simple example that associates a trigger with a table
- for <literal>INSERT</literal> statements. It acts as an
- accumulator to sum the values inserted into one of the columns of
- the table.
- </para>
-
- <para>
- The following statements create a table and a trigger for it:
- </para>
-
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS account;
+Query OK, 0 rows affected, 1 warning (0.01 sec)
+-->
mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
+Query OK, 0 rows affected (0.03 sec)
+
mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
-> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
+Query OK, 0 rows affected (0.06 sec)
</programlisting>
- <para>
- The <literal>CREATE TRIGGER</literal> statement creates a trigger
- named <literal>ins_sum</literal> that is associated with the
- <literal>account</literal> table. It also includes clauses that
- specify the trigger activation time, the triggering event, and
- what to do with the trigger activates:
- </para>
+ <para>
+ The <literal>CREATE TRIGGER</literal> statement creates a trigger
+ named <literal>ins_sum</literal> that is associated with the
+ <literal>account</literal> table. It also includes clauses that
+ specify the trigger activation time, the triggering event, and what
+ to do with the trigger activates:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The keyword <literal>INSERT</literal> indicates the event that
- activates the trigger. In the example,
- <literal>INSERT</literal> statements cause trigger activation.
- You can also create triggers for <literal>DELETE</literal> and
- <literal>UPDATE</literal> statements.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>INSERT</literal> indicates the event that
+ activates the trigger. In the example, <literal>INSERT</literal>
+ statements cause trigger activation. You can also create
+ triggers for <literal>DELETE</literal> and
+ <literal>UPDATE</literal> statements.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The statement following <literal>FOR EACH ROW</literal>
- 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 <literal>SET</literal> that accumulates the values
- inserted into the <literal>amount</literal> column. The
- statement refers to the column as
- <literal>NEW.amount</literal> which means <quote>the value of
- the <literal>amount</literal> column to be inserted into the
- new row.</quote>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The statement following <literal>FOR EACH ROW</literal> 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
+ <literal>SET</literal> that accumulates the values inserted into
+ the <literal>amount</literal> column. The statement refers to
+ the column as <literal>NEW.amount</literal> which means
+ <quote>the value of the <literal>amount</literal> column to be
+ inserted into the new row.</quote>
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- To use the trigger, set the accumulator variable to zero, execute
- an <literal>INSERT</literal> statement, and then see what value
- the variable has afterward:
- </para>
+ <para>
+ To use the trigger, set the accumulator variable to zero, execute an
+ <literal>INSERT</literal> statement, and then see what value the
+ variable has afterward:
+ </para>
<programlisting>
mysql> <userinput>SET @sum = 0;</userinput>
@@ -204,110 +163,109 @@
+-----------------------+
</programlisting>
- <para>
- In this case, the value of <literal>@sum</literal> after the
- <literal>INSERT</literal> statement has executed is <literal>14.98
- + 1937.50 - 100</literal>, or <literal>1852.48</literal>.
- </para>
+ <para>
+ In this case, the value of <literal>@sum</literal> after the
+ <literal>INSERT</literal> statement has executed is <literal>14.98 +
+ 1937.50 - 100</literal>, or <literal>1852.48</literal>.
+ </para>
- <para>
- To destroy the trigger, use a <literal>DROP TRIGGER</literal>
- statement. You must specify the schema name if the trigger is not
- in the default schema:
- </para>
+ <para>
+ To destroy the trigger, use a <literal>DROP TRIGGER</literal>
+ statement. You must specify the schema name if the trigger is not in
+ the default schema:
+ </para>
<programlisting>
mysql> <userinput>DROP TRIGGER test.ins_sum;</userinput>
</programlisting>
- <para>
- Triggers for a table are also dropped if you drop the table.
- </para>
+ <para>
+ Triggers for a table are also dropped if you drop the table.
+ </para>
- <para>
- Trigger 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.
- </para>
+ <para>
+ Trigger 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.
+ </para>
- <para>
- 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 activation time and activation event. For
- example, you cannot define two <literal>BEFORE INSERT</literal>
- triggers or two <literal>AFTER UPDATE</literal> 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 <literal>BEGIN ... END</literal> compound statement
- construct after <literal>FOR EACH ROW</literal>. (An example
- appears later in this section.)
- </para>
+ <para>
+ 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 activation time and activation event. For example, you
+ cannot define two <literal>BEFORE INSERT</literal> triggers or two
+ <literal>AFTER UPDATE</literal> 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
+ <literal>BEGIN ... END</literal> compound statement construct after
+ <literal>FOR EACH ROW</literal>. (An example appears later in this
+ section.)
+ </para>
- <para>
- The <literal>OLD</literal> and <literal>NEW</literal> keywords
- enable you to access columns in the rows affected by a trigger.
- (<literal>OLD</literal> and <literal>NEW</literal> are not case
- sensitive.) In an <literal>INSERT</literal> trigger, only
- <literal>NEW.<replaceable>col_name</replaceable></literal> can be
- used; there is no old row. In a <literal>DELETE</literal> trigger,
- only <literal>OLD.<replaceable>col_name</replaceable></literal>
- can be used; there is no new row. In an <literal>UPDATE</literal>
- trigger, you can use
- <literal>OLD.<replaceable>col_name</replaceable></literal> to
- refer to the columns of a row before it is updated and
- <literal>NEW.<replaceable>col_name</replaceable></literal> to
- refer to the columns of the row after it is updated.
- </para>
+ <para>
+ The <literal>OLD</literal> and <literal>NEW</literal> keywords
+ enable you to access columns in the rows affected by a trigger.
+ (<literal>OLD</literal> and <literal>NEW</literal> are not case
+ sensitive.) In an <literal>INSERT</literal> trigger, only
+ <literal>NEW.<replaceable>col_name</replaceable></literal> can be
+ used; there is no old row. In a <literal>DELETE</literal> trigger,
+ only <literal>OLD.<replaceable>col_name</replaceable></literal> can
+ be used; there is no new row. In an <literal>UPDATE</literal>
+ trigger, you can use
+ <literal>OLD.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of a row before it is updated and
+ <literal>NEW.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of the row after it is updated.
+ </para>
- <para>
- A column named with <literal>OLD</literal> is read only. You can
- refer to it (if you have the <literal>SELECT</literal> privilege),
- but not modify it. A column named with <literal>NEW</literal> can
- be referred to if you have the <literal>SELECT</literal> privilege
- for it. In a <literal>BEFORE</literal> trigger, you can also
- change its value with <literal>SET
- NEW.<replaceable>col_name</replaceable> =
- <replaceable>value</replaceable></literal> if you have the
- <literal>UPDATE</literal> 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.
- </para>
+ <para>
+ A column named with <literal>OLD</literal> is read only. You can
+ refer to it (if you have the <literal>SELECT</literal> privilege),
+ but not modify it. A column named with <literal>NEW</literal> can be
+ referred to if you have the <literal>SELECT</literal> privilege for
+ it. In a <literal>BEFORE</literal> trigger, you can also change its
+ value with <literal>SET NEW.<replaceable>col_name</replaceable> =
+ <replaceable>value</replaceable></literal> if you have the
+ <literal>UPDATE</literal> 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.
+ </para>
- <para>
- In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
- value for an <literal>AUTO_INCREMENT</literal> column is 0, not
- the automatically generated sequence number that will be generated
- when the new record actually is inserted.
- </para>
+ <para>
+ In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
+ value for an <literal>AUTO_INCREMENT</literal> column is 0, not the
+ automatically generated sequence number that will be generated when
+ the new record actually is inserted.
+ </para>
- <para>
- <literal>OLD</literal> and <literal>NEW</literal> are MySQL
- extensions to triggers.
- </para>
+ <para>
+ <literal>OLD</literal> and <literal>NEW</literal> are MySQL
+ extensions to triggers.
+ </para>
- <remark role="todo">
- [js] Add an example demonstrating a multiple-table insert, since
- we now link to this section from a FAQ question concerning these.
- </remark>
+ <remark role="todo">
+ [js] Add an example demonstrating a multiple-table insert, since we
+ now link to this section from a FAQ question concerning these.
+ </remark>
- <para>
- By using the <literal>BEGIN ... END</literal> construct, you can
- define a trigger that executes multiple statements. Within the
- <literal>BEGIN</literal> block, you also can use other syntax that
- is allowed within stored routines such as conditionals and loops.
- However, just as for stored routines, if you use the
- <command>mysql</command> program to define a trigger that executes
- multiple statements, it is necessary to redefine the
- <command>mysql</command> statement delimiter so that you can use
- the <literal>;</literal> statement delimiter within the trigger
- definition. The following example illustrates these points. It
- defines an <literal>UPDATE</literal> 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
- <literal>BEFORE</literal> trigger because the value needs to be
- checked before it is used to update the row:
- </para>
+ <para>
+ By using the <literal>BEGIN ... END</literal> construct, you can
+ define a trigger that executes multiple statements. Within the
+ <literal>BEGIN</literal> block, you also can use other syntax that
+ is allowed within stored routines such as conditionals and loops.
+ However, just as for stored routines, if you use the
+ <command>mysql</command> program to define a trigger that executes
+ multiple statements, it is necessary to redefine the
+ <command>mysql</command> statement delimiter so that you can use the
+ <literal>;</literal> statement delimiter within the trigger
+ definition. The following example illustrates these points. It
+ defines an <literal>UPDATE</literal> 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
+ <literal>BEFORE</literal> trigger because the value needs to be
+ checked before it is used to update the row:
+ </para>
<programlisting>
mysql> <userinput>delimiter //</userinput>
@@ -323,103 +281,100 @@
mysql> <userinput>delimiter ;</userinput>
</programlisting>
- <para>
- It can be easier to define a stored procedure separately and then
- invoke it from the trigger using a simple <literal>CALL</literal>
- statement. This is also advantageous if you want to invoke the
- same routine from within several triggers.
- </para>
+ <para>
+ It can be easier to define a stored procedure separately and then
+ invoke it from the trigger using a simple <literal>CALL</literal>
+ statement. This is also advantageous if you want to invoke the same
+ routine from within several triggers.
+ </para>
- <remark role="todo">
- [MH] Let's get an example of this created when we have time.
- </remark>
+ <remark role="todo">
+ [MH] Let's get an example of this created when we have time.
+ </remark>
- <para>
- There are some limitations on what can appear in statements that a
- trigger executes when activated:
- </para>
+ <para>
+ There are some limitations on what can appear in statements that a
+ trigger executes when activated:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The trigger cannot use the <literal>CALL</literal> statement
- to invoke stored procedures that return data to the client or
- that use dynamic SQL. (Stored procedures are allowed to return
- data to the trigger through <literal>OUT</literal> or
- <literal>INOUT</literal> parameters.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use the <literal>CALL</literal> statement to
+ invoke stored procedures that return data to the client or that
+ use dynamic SQL. (Stored procedures are allowed to return data
+ to the trigger through <literal>OUT</literal> or
+ <literal>INOUT</literal> parameters.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- The trigger cannot use statements that explicitly or
- implicitly begin or end a transaction such as <literal>START
- TRANSACTION</literal>, <literal>COMMIT</literal>, or
- <literal>ROLLBACK</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use statements that explicitly or implicitly
+ begin or end a transaction such as <literal>START
+ TRANSACTION</literal>, <literal>COMMIT</literal>, or
+ <literal>ROLLBACK</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Prior to MySQL 5.0.10, triggers cannot contain direct
- references to tables by name.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Prior to MySQL 5.0.10, triggers cannot contain direct references
+ to tables by name.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- MySQL handles errors during trigger execution as follows:
- </para>
+ <para>
+ MySQL handles errors during trigger execution as follows:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- If a <literal>BEFORE</literal> trigger fails, the operation on
- the corresponding row is not performed.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If a <literal>BEFORE</literal> trigger fails, the operation on
+ the corresponding row is not performed.
+ </para>
+ </listitem>
- <listitem>
- <para>
- A <literal>BEFORE</literal> trigger is activated by the
- <emphasis>attempt</emphasis> to insert or modify the row,
- regardless of whether the attempt subsequently succeeds.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ A <literal>BEFORE</literal> trigger is activated by the
+ <emphasis>attempt</emphasis> to insert or modify the row,
+ regardless of whether the attempt subsequently succeeds.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An <literal>AFTER</literal> trigger is executed only if the
- <literal>BEFORE</literal> trigger (if any) and the row
- operation both execute successfully.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An <literal>AFTER</literal> trigger is executed only if the
+ <literal>BEFORE</literal> trigger (if any) and the row operation
+ both execute successfully.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An error during either a <literal>BEFORE</literal> or
- <literal>AFTER</literal> trigger results in failure of the
- entire statement that caused trigger invocation.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An error during either a <literal>BEFORE</literal> or
+ <literal>AFTER</literal> trigger results in failure of the
+ entire statement that caused trigger invocation.
+ </para>
+ </listitem>
- <listitem>
- <para>
- For transactional tables, failure of a statement should cause
- rollback of all changes performed by the statement. Failure of
- a trigger causes the statement to fail, so trigger failure
- also causes rollback. 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.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ For transactional tables, failure of a statement should cause
+ rollback of all changes performed by the statement. Failure of a
+ trigger causes the statement to fail, so trigger failure also
+ causes rollback. 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.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
Modified: trunk/refman-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-5.1/renamed-nodes.txt 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 549 bytes
@@ -574,6 +574,7 @@
upgrading-grant-tables mysql-fix-privilege-tables
using-mysql-programs programs-using 2009-01-01
using-mysqlcheck mysqlcheck
+using-triggers triggers 2009-01-12
utility-statements sql-syntax-utility 2009-07-14
varaible-table variables-table
variable-table variables-table
Modified: trunk/refman-5.1/triggers.xml
===================================================================
--- trunk/refman-5.1/triggers.xml 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-5.1/triggers.xml 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 4, Lines Added: 241, Lines Deleted: 286; 24180 bytes
@@ -5,33 +5,29 @@
]>
<section id="triggers">
- <title>Triggers</title>
+ <title>Using Triggers</title>
<indexterm>
<primary>triggers</primary>
</indexterm>
<para>
- A trigger is a named database object that is associated with a table
- and that is activated when a particular event occurs for the table.
- For example, the following statements create a table and an
- <literal>INSERT</literal> trigger. The trigger sums the values
- inserted into one of the table's columns:
+ 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.
</para>
-<programlisting>
-<!--
-mysql> DROP TABLE IF EXISTS account;
-Query OK, 0 rows affected, 1 warning (0.01 sec)
--->
-mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
-Query OK, 0 rows affected (0.03 sec)
+ <para>
+ A trigger is defined to activate when an <literal>INSERT</literal>,
+ <literal>DELETE</literal>, or <literal>UPDATE</literal> statement
+ executes for the associated table. 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 inserted into a
+ table or after each row that is updated.
+ </para>
-mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
- -> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
-Query OK, 0 rows affected (0.06 sec)
-</programlisting>
-
<important>
<para>
MySQL triggers are activated by SQL statements
@@ -76,121 +72,84 @@
</itemizedlist>
- <section id="using-triggers">
+ <para>
+ To create a trigger or drop a trigger, use the <literal>CREATE
+ TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement. The
+ syntax for these statements is described in
+ <xref linkend="create-trigger"/>, and
+ <xref linkend="drop-trigger"/>.
+ </para>
- <title>Using Triggers</title>
+ <para>
+ Here is a simple example that associates a trigger with a table for
+ <literal>INSERT</literal> statements. The trigger acts as an
+ accumulator, summing the values inserted into one of the columns of
+ the table.
+ </para>
- <para>
- This section discusses how to use triggers in MySQL
- ¤t-series; and some limitations regarding their use.
- Additional information about trigger limitations is given in
- <xref linkend="routine-restrictions"/>.
- </para>
-
- <para>
- 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.
- </para>
-
- <para>
- A trigger is associated with a table and is defined to activate
- when an <literal>INSERT</literal>, <literal>DELETE</literal>, or
- <literal>UPDATE</literal> 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.
- </para>
-
- <important>
- <para>
- MySQL triggers are activated by SQL statements
- <emphasis>only</emphasis>. They are not activated by changes in
- tables made by APIs that do not transmit SQL statements to the
- MySQL Server; in particular, they are not activated by updates
- made using the <literal>NDB</literal> API.
- </para>
- </important>
-
- <para>
- To create a trigger or drop a trigger, use the <literal>CREATE
- TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement.
- The syntax for these statements is described in
- <xref linkend="create-trigger"/>, and
- <xref linkend="drop-trigger"/>.
- </para>
-
- <para>
- Here is a simple example that associates a trigger with a table
- for <literal>INSERT</literal> statements. It acts as an
- accumulator to sum the values inserted into one of the columns of
- the table.
- </para>
-
- <para>
- The following statements create a table and a trigger for it:
- </para>
-
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS account;
+Query OK, 0 rows affected, 1 warning (0.01 sec)
+-->
mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
+Query OK, 0 rows affected (0.03 sec)
+
mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
-> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
+Query OK, 0 rows affected (0.06 sec)
</programlisting>
- <para>
- The <literal>CREATE TRIGGER</literal> statement creates a trigger
- named <literal>ins_sum</literal> that is associated with the
- <literal>account</literal> table. It also includes clauses that
- specify the trigger activation time, the triggering event, and
- what to do with the trigger activates:
- </para>
+ <para>
+ The <literal>CREATE TRIGGER</literal> statement creates a trigger
+ named <literal>ins_sum</literal> that is associated with the
+ <literal>account</literal> table. It also includes clauses that
+ specify the trigger activation time, the triggering event, and what
+ to do with the trigger activates:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The keyword <literal>INSERT</literal> indicates the event that
- activates the trigger. In the example,
- <literal>INSERT</literal> statements cause trigger activation.
- You can also create triggers for <literal>DELETE</literal> and
- <literal>UPDATE</literal> statements.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>INSERT</literal> indicates the event that
+ activates the trigger. In the example, <literal>INSERT</literal>
+ statements cause trigger activation. You can also create
+ triggers for <literal>DELETE</literal> and
+ <literal>UPDATE</literal> statements.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The statement following <literal>FOR EACH ROW</literal>
- 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 <literal>SET</literal> that accumulates the values
- inserted into the <literal>amount</literal> column. The
- statement refers to the column as
- <literal>NEW.amount</literal> which means <quote>the value of
- the <literal>amount</literal> column to be inserted into the
- new row.</quote>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The statement following <literal>FOR EACH ROW</literal> 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
+ <literal>SET</literal> that accumulates the values inserted into
+ the <literal>amount</literal> column. The statement refers to
+ the column as <literal>NEW.amount</literal> which means
+ <quote>the value of the <literal>amount</literal> column to be
+ inserted into the new row.</quote>
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- To use the trigger, set the accumulator variable to zero, execute
- an <literal>INSERT</literal> statement, and then see what value
- the variable has afterward:
- </para>
+ <para>
+ To use the trigger, set the accumulator variable to zero, execute an
+ <literal>INSERT</literal> statement, and then see what value the
+ variable has afterward:
+ </para>
<programlisting>
mysql> <userinput>SET @sum = 0;</userinput>
@@ -203,110 +162,109 @@
+-----------------------+
</programlisting>
- <para>
- In this case, the value of <literal>@sum</literal> after the
- <literal>INSERT</literal> statement has executed is <literal>14.98
- + 1937.50 - 100</literal>, or <literal>1852.48</literal>.
- </para>
+ <para>
+ In this case, the value of <literal>@sum</literal> after the
+ <literal>INSERT</literal> statement has executed is <literal>14.98 +
+ 1937.50 - 100</literal>, or <literal>1852.48</literal>.
+ </para>
- <para>
- To destroy the trigger, use a <literal>DROP TRIGGER</literal>
- statement. You must specify the schema name if the trigger is not
- in the default schema:
- </para>
+ <para>
+ To destroy the trigger, use a <literal>DROP TRIGGER</literal>
+ statement. You must specify the schema name if the trigger is not in
+ the default schema:
+ </para>
<programlisting>
mysql> <userinput>DROP TRIGGER test.ins_sum;</userinput>
</programlisting>
- <para>
- Triggers for a table are also dropped if you drop the table.
- </para>
+ <para>
+ Triggers for a table are also dropped if you drop the table.
+ </para>
- <para>
- Trigger 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.
- </para>
+ <para>
+ Trigger 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.
+ </para>
- <para>
- 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 activation time and activation event. For
- example, you cannot define two <literal>BEFORE INSERT</literal>
- triggers or two <literal>AFTER UPDATE</literal> 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 <literal>BEGIN ... END</literal> compound statement
- construct after <literal>FOR EACH ROW</literal>. (An example
- appears later in this section.)
- </para>
+ <para>
+ 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 activation time and activation event. For example, you
+ cannot define two <literal>BEFORE INSERT</literal> triggers or two
+ <literal>AFTER UPDATE</literal> 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
+ <literal>BEGIN ... END</literal> compound statement construct after
+ <literal>FOR EACH ROW</literal>. (An example appears later in this
+ section.)
+ </para>
- <para>
- The <literal>OLD</literal> and <literal>NEW</literal> keywords
- enable you to access columns in the rows affected by a trigger.
- (<literal>OLD</literal> and <literal>NEW</literal> are not case
- sensitive.) In an <literal>INSERT</literal> trigger, only
- <literal>NEW.<replaceable>col_name</replaceable></literal> can be
- used; there is no old row. In a <literal>DELETE</literal> trigger,
- only <literal>OLD.<replaceable>col_name</replaceable></literal>
- can be used; there is no new row. In an <literal>UPDATE</literal>
- trigger, you can use
- <literal>OLD.<replaceable>col_name</replaceable></literal> to
- refer to the columns of a row before it is updated and
- <literal>NEW.<replaceable>col_name</replaceable></literal> to
- refer to the columns of the row after it is updated.
- </para>
+ <para>
+ The <literal>OLD</literal> and <literal>NEW</literal> keywords
+ enable you to access columns in the rows affected by a trigger.
+ (<literal>OLD</literal> and <literal>NEW</literal> are not case
+ sensitive.) In an <literal>INSERT</literal> trigger, only
+ <literal>NEW.<replaceable>col_name</replaceable></literal> can be
+ used; there is no old row. In a <literal>DELETE</literal> trigger,
+ only <literal>OLD.<replaceable>col_name</replaceable></literal> can
+ be used; there is no new row. In an <literal>UPDATE</literal>
+ trigger, you can use
+ <literal>OLD.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of a row before it is updated and
+ <literal>NEW.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of the row after it is updated.
+ </para>
- <para>
- A column named with <literal>OLD</literal> is read only. You can
- refer to it (if you have the <literal>SELECT</literal> privilege),
- but not modify it. A column named with <literal>NEW</literal> can
- be referred to if you have the <literal>SELECT</literal> privilege
- for it. In a <literal>BEFORE</literal> trigger, you can also
- change its value with <literal>SET
- NEW.<replaceable>col_name</replaceable> =
- <replaceable>value</replaceable></literal> if you have the
- <literal>UPDATE</literal> 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.
- </para>
+ <para>
+ A column named with <literal>OLD</literal> is read only. You can
+ refer to it (if you have the <literal>SELECT</literal> privilege),
+ but not modify it. A column named with <literal>NEW</literal> can be
+ referred to if you have the <literal>SELECT</literal> privilege for
+ it. In a <literal>BEFORE</literal> trigger, you can also change its
+ value with <literal>SET NEW.<replaceable>col_name</replaceable> =
+ <replaceable>value</replaceable></literal> if you have the
+ <literal>UPDATE</literal> 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.
+ </para>
- <para>
- In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
- value for an <literal>AUTO_INCREMENT</literal> column is 0, not
- the automatically generated sequence number that will be generated
- when the new record actually is inserted.
- </para>
+ <para>
+ In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
+ value for an <literal>AUTO_INCREMENT</literal> column is 0, not the
+ automatically generated sequence number that will be generated when
+ the new record actually is inserted.
+ </para>
- <para>
- <literal>OLD</literal> and <literal>NEW</literal> are MySQL
- extensions to triggers.
- </para>
+ <para>
+ <literal>OLD</literal> and <literal>NEW</literal> are MySQL
+ extensions to triggers.
+ </para>
- <remark role="todo">
- [js] Add an example demonstrating a multiple-table insert, since
- we now link to this section from a FAQ question concerning these.
- </remark>
+ <remark role="todo">
+ [js] Add an example demonstrating a multiple-table insert, since we
+ now link to this section from a FAQ question concerning these.
+ </remark>
- <para>
- By using the <literal>BEGIN ... END</literal> construct, you can
- define a trigger that executes multiple statements. Within the
- <literal>BEGIN</literal> block, you also can use other syntax that
- is allowed within stored routines such as conditionals and loops.
- However, just as for stored routines, if you use the
- <command>mysql</command> program to define a trigger that executes
- multiple statements, it is necessary to redefine the
- <command>mysql</command> statement delimiter so that you can use
- the <literal>;</literal> statement delimiter within the trigger
- definition. The following example illustrates these points. It
- defines an <literal>UPDATE</literal> 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
- <literal>BEFORE</literal> trigger because the value needs to be
- checked before it is used to update the row:
- </para>
+ <para>
+ By using the <literal>BEGIN ... END</literal> construct, you can
+ define a trigger that executes multiple statements. Within the
+ <literal>BEGIN</literal> block, you also can use other syntax that
+ is allowed within stored routines such as conditionals and loops.
+ However, just as for stored routines, if you use the
+ <command>mysql</command> program to define a trigger that executes
+ multiple statements, it is necessary to redefine the
+ <command>mysql</command> statement delimiter so that you can use the
+ <literal>;</literal> statement delimiter within the trigger
+ definition. The following example illustrates these points. It
+ defines an <literal>UPDATE</literal> 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
+ <literal>BEFORE</literal> trigger because the value needs to be
+ checked before it is used to update the row:
+ </para>
<programlisting>
mysql> <userinput>delimiter //</userinput>
@@ -322,96 +280,93 @@
mysql> <userinput>delimiter ;</userinput>
</programlisting>
- <para>
- It can be easier to define a stored procedure separately and then
- invoke it from the trigger using a simple <literal>CALL</literal>
- statement. This is also advantageous if you want to invoke the
- same routine from within several triggers.
- </para>
+ <para>
+ It can be easier to define a stored procedure separately and then
+ invoke it from the trigger using a simple <literal>CALL</literal>
+ statement. This is also advantageous if you want to invoke the same
+ routine from within several triggers.
+ </para>
- <remark role="todo">
- [MH] Let's get an example of this created when we have time.
- </remark>
+ <remark role="todo">
+ [MH] Let's get an example of this created when we have time.
+ </remark>
- <para>
- There are some limitations on what can appear in statements that a
- trigger executes when activated:
- </para>
+ <para>
+ There are some limitations on what can appear in statements that a
+ trigger executes when activated:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The trigger cannot use the <literal>CALL</literal> statement
- to invoke stored procedures that return data to the client or
- that use dynamic SQL. (Stored procedures are allowed to return
- data to the trigger through <literal>OUT</literal> or
- <literal>INOUT</literal> parameters.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use the <literal>CALL</literal> statement to
+ invoke stored procedures that return data to the client or that
+ use dynamic SQL. (Stored procedures are allowed to return data
+ to the trigger through <literal>OUT</literal> or
+ <literal>INOUT</literal> parameters.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- The trigger cannot use statements that explicitly or
- implicitly begin or end a transaction such as <literal>START
- TRANSACTION</literal>, <literal>COMMIT</literal>, or
- <literal>ROLLBACK</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use statements that explicitly or implicitly
+ begin or end a transaction such as <literal>START
+ TRANSACTION</literal>, <literal>COMMIT</literal>, or
+ <literal>ROLLBACK</literal>.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- MySQL handles errors during trigger execution as follows:
- </para>
+ <para>
+ MySQL handles errors during trigger execution as follows:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- If a <literal>BEFORE</literal> trigger fails, the operation on
- the corresponding row is not performed.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If a <literal>BEFORE</literal> trigger fails, the operation on
+ the corresponding row is not performed.
+ </para>
+ </listitem>
- <listitem>
- <para>
- A <literal>BEFORE</literal> trigger is activated by the
- <emphasis>attempt</emphasis> to insert or modify the row,
- regardless of whether the attempt subsequently succeeds.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ A <literal>BEFORE</literal> trigger is activated by the
+ <emphasis>attempt</emphasis> to insert or modify the row,
+ regardless of whether the attempt subsequently succeeds.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An <literal>AFTER</literal> trigger is executed only if the
- <literal>BEFORE</literal> trigger (if any) and the row
- operation both execute successfully.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An <literal>AFTER</literal> trigger is executed only if the
+ <literal>BEFORE</literal> trigger (if any) and the row operation
+ both execute successfully.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An error during either a <literal>BEFORE</literal> or
- <literal>AFTER</literal> trigger results in failure of the
- entire statement that caused trigger invocation.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An error during either a <literal>BEFORE</literal> or
+ <literal>AFTER</literal> trigger results in failure of the
+ entire statement that caused trigger invocation.
+ </para>
+ </listitem>
- <listitem>
- <para>
- For transactional tables, failure of a statement should cause
- rollback of all changes performed by the statement. Failure of
- a trigger causes the statement to fail, so trigger failure
- also causes rollback. 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.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ For transactional tables, failure of a statement should cause
+ rollback of all changes performed by the statement. Failure of a
+ trigger causes the statement to fail, so trigger failure also
+ causes rollback. 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.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
Modified: trunk/refman-6.0/renamed-nodes.txt
===================================================================
--- trunk/refman-6.0/renamed-nodes.txt 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-6.0/renamed-nodes.txt 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 551 bytes
@@ -254,5 +254,5 @@
tcl apis-tcl 2009-09-01
transactional-commands sql-syntax-transactions 2009-02-12
transactional-statements sql-syntax-transactions 2009-07-14
-using-mysql-programs programs-using 2009-01-01
+using-triggers triggers 2009-01-12
utility-statements sql-syntax-utility 2009-07-14
Modified: trunk/refman-6.0/triggers.xml
===================================================================
--- trunk/refman-6.0/triggers.xml 2008-09-12 15:35:02 UTC (rev 11794)
+++ trunk/refman-6.0/triggers.xml 2008-09-12 15:49:47 UTC (rev 11795)
Changed blocks: 4, Lines Added: 241, Lines Deleted: 286; 24180 bytes
@@ -5,33 +5,29 @@
]>
<section id="triggers">
- <title>Triggers</title>
+ <title>Using Triggers</title>
<indexterm>
<primary>triggers</primary>
</indexterm>
<para>
- A trigger is a named database object that is associated with a table
- and that is activated when a particular event occurs for the table.
- For example, the following statements create a table and an
- <literal>INSERT</literal> trigger. The trigger sums the values
- inserted into one of the table's columns:
+ 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.
</para>
-<programlisting>
-<!--
-mysql> DROP TABLE IF EXISTS account;
-Query OK, 0 rows affected, 1 warning (0.01 sec)
--->
-mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
-Query OK, 0 rows affected (0.03 sec)
+ <para>
+ A trigger is defined to activate when an <literal>INSERT</literal>,
+ <literal>DELETE</literal>, or <literal>UPDATE</literal> statement
+ executes for the associated table. 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 inserted into a
+ table or after each row that is updated.
+ </para>
-mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
- -> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
-Query OK, 0 rows affected (0.06 sec)
-</programlisting>
-
<important>
<para>
MySQL triggers are activated by SQL statements
@@ -76,121 +72,84 @@
</itemizedlist>
- <section id="using-triggers">
+ <para>
+ To create a trigger or drop a trigger, use the <literal>CREATE
+ TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement. The
+ syntax for these statements is described in
+ <xref linkend="create-trigger"/>, and
+ <xref linkend="drop-trigger"/>.
+ </para>
- <title>Using Triggers</title>
+ <para>
+ Here is a simple example that associates a trigger with a table for
+ <literal>INSERT</literal> statements. The trigger acts as an
+ accumulator, summing the values inserted into one of the columns of
+ the table.
+ </para>
- <para>
- This section discusses how to use triggers in MySQL
- ¤t-series; and some limitations regarding their use.
- Additional information about trigger limitations is given in
- <xref linkend="routine-restrictions"/>.
- </para>
-
- <para>
- 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.
- </para>
-
- <para>
- A trigger is associated with a table and is defined to activate
- when an <literal>INSERT</literal>, <literal>DELETE</literal>, or
- <literal>UPDATE</literal> 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.
- </para>
-
- <important>
- <para>
- MySQL triggers are activated by SQL statements
- <emphasis>only</emphasis>. They are not activated by changes in
- tables made by APIs that do not transmit SQL statements to the
- MySQL Server; in particular, they are not activated by updates
- made using the <literal>NDB</literal> API.
- </para>
- </important>
-
- <para>
- To create a trigger or drop a trigger, use the <literal>CREATE
- TRIGGER</literal> or <literal>DROP TRIGGER</literal> statement.
- The syntax for these statements is described in
- <xref linkend="create-trigger"/>, and
- <xref linkend="drop-trigger"/>.
- </para>
-
- <para>
- Here is a simple example that associates a trigger with a table
- for <literal>INSERT</literal> statements. It acts as an
- accumulator to sum the values inserted into one of the columns of
- the table.
- </para>
-
- <para>
- The following statements create a table and a trigger for it:
- </para>
-
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS account;
+Query OK, 0 rows affected, 1 warning (0.01 sec)
+-->
mysql> <userinput>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</userinput>
+Query OK, 0 rows affected (0.03 sec)
+
mysql> <userinput>CREATE TRIGGER ins_sum BEFORE INSERT ON account</userinput>
-> <userinput>FOR EACH ROW SET @sum = @sum + NEW.amount;</userinput>
+Query OK, 0 rows affected (0.06 sec)
</programlisting>
- <para>
- The <literal>CREATE TRIGGER</literal> statement creates a trigger
- named <literal>ins_sum</literal> that is associated with the
- <literal>account</literal> table. It also includes clauses that
- specify the trigger activation time, the triggering event, and
- what to do with the trigger activates:
- </para>
+ <para>
+ The <literal>CREATE TRIGGER</literal> statement creates a trigger
+ named <literal>ins_sum</literal> that is associated with the
+ <literal>account</literal> table. It also includes clauses that
+ specify the trigger activation time, the triggering event, and what
+ to do with the trigger activates:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>BEFORE</literal> 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 <literal>AFTER</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The keyword <literal>INSERT</literal> indicates the event that
- activates the trigger. In the example,
- <literal>INSERT</literal> statements cause trigger activation.
- You can also create triggers for <literal>DELETE</literal> and
- <literal>UPDATE</literal> statements.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The keyword <literal>INSERT</literal> indicates the event that
+ activates the trigger. In the example, <literal>INSERT</literal>
+ statements cause trigger activation. You can also create
+ triggers for <literal>DELETE</literal> and
+ <literal>UPDATE</literal> statements.
+ </para>
+ </listitem>
- <listitem>
- <para>
- The statement following <literal>FOR EACH ROW</literal>
- 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 <literal>SET</literal> that accumulates the values
- inserted into the <literal>amount</literal> column. The
- statement refers to the column as
- <literal>NEW.amount</literal> which means <quote>the value of
- the <literal>amount</literal> column to be inserted into the
- new row.</quote>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The statement following <literal>FOR EACH ROW</literal> 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
+ <literal>SET</literal> that accumulates the values inserted into
+ the <literal>amount</literal> column. The statement refers to
+ the column as <literal>NEW.amount</literal> which means
+ <quote>the value of the <literal>amount</literal> column to be
+ inserted into the new row.</quote>
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- To use the trigger, set the accumulator variable to zero, execute
- an <literal>INSERT</literal> statement, and then see what value
- the variable has afterward:
- </para>
+ <para>
+ To use the trigger, set the accumulator variable to zero, execute an
+ <literal>INSERT</literal> statement, and then see what value the
+ variable has afterward:
+ </para>
<programlisting>
mysql> <userinput>SET @sum = 0;</userinput>
@@ -203,110 +162,109 @@
+-----------------------+
</programlisting>
- <para>
- In this case, the value of <literal>@sum</literal> after the
- <literal>INSERT</literal> statement has executed is <literal>14.98
- + 1937.50 - 100</literal>, or <literal>1852.48</literal>.
- </para>
+ <para>
+ In this case, the value of <literal>@sum</literal> after the
+ <literal>INSERT</literal> statement has executed is <literal>14.98 +
+ 1937.50 - 100</literal>, or <literal>1852.48</literal>.
+ </para>
- <para>
- To destroy the trigger, use a <literal>DROP TRIGGER</literal>
- statement. You must specify the schema name if the trigger is not
- in the default schema:
- </para>
+ <para>
+ To destroy the trigger, use a <literal>DROP TRIGGER</literal>
+ statement. You must specify the schema name if the trigger is not in
+ the default schema:
+ </para>
<programlisting>
mysql> <userinput>DROP TRIGGER test.ins_sum;</userinput>
</programlisting>
- <para>
- Triggers for a table are also dropped if you drop the table.
- </para>
+ <para>
+ Triggers for a table are also dropped if you drop the table.
+ </para>
- <para>
- Trigger 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.
- </para>
+ <para>
+ Trigger 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.
+ </para>
- <para>
- 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 activation time and activation event. For
- example, you cannot define two <literal>BEFORE INSERT</literal>
- triggers or two <literal>AFTER UPDATE</literal> 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 <literal>BEGIN ... END</literal> compound statement
- construct after <literal>FOR EACH ROW</literal>. (An example
- appears later in this section.)
- </para>
+ <para>
+ 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 activation time and activation event. For example, you
+ cannot define two <literal>BEFORE INSERT</literal> triggers or two
+ <literal>AFTER UPDATE</literal> 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
+ <literal>BEGIN ... END</literal> compound statement construct after
+ <literal>FOR EACH ROW</literal>. (An example appears later in this
+ section.)
+ </para>
- <para>
- The <literal>OLD</literal> and <literal>NEW</literal> keywords
- enable you to access columns in the rows affected by a trigger.
- (<literal>OLD</literal> and <literal>NEW</literal> are not case
- sensitive.) In an <literal>INSERT</literal> trigger, only
- <literal>NEW.<replaceable>col_name</replaceable></literal> can be
- used; there is no old row. In a <literal>DELETE</literal> trigger,
- only <literal>OLD.<replaceable>col_name</replaceable></literal>
- can be used; there is no new row. In an <literal>UPDATE</literal>
- trigger, you can use
- <literal>OLD.<replaceable>col_name</replaceable></literal> to
- refer to the columns of a row before it is updated and
- <literal>NEW.<replaceable>col_name</replaceable></literal> to
- refer to the columns of the row after it is updated.
- </para>
+ <para>
+ The <literal>OLD</literal> and <literal>NEW</literal> keywords
+ enable you to access columns in the rows affected by a trigger.
+ (<literal>OLD</literal> and <literal>NEW</literal> are not case
+ sensitive.) In an <literal>INSERT</literal> trigger, only
+ <literal>NEW.<replaceable>col_name</replaceable></literal> can be
+ used; there is no old row. In a <literal>DELETE</literal> trigger,
+ only <literal>OLD.<replaceable>col_name</replaceable></literal> can
+ be used; there is no new row. In an <literal>UPDATE</literal>
+ trigger, you can use
+ <literal>OLD.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of a row before it is updated and
+ <literal>NEW.<replaceable>col_name</replaceable></literal> to refer
+ to the columns of the row after it is updated.
+ </para>
- <para>
- A column named with <literal>OLD</literal> is read only. You can
- refer to it (if you have the <literal>SELECT</literal> privilege),
- but not modify it. A column named with <literal>NEW</literal> can
- be referred to if you have the <literal>SELECT</literal> privilege
- for it. In a <literal>BEFORE</literal> trigger, you can also
- change its value with <literal>SET
- NEW.<replaceable>col_name</replaceable> =
- <replaceable>value</replaceable></literal> if you have the
- <literal>UPDATE</literal> 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.
- </para>
+ <para>
+ A column named with <literal>OLD</literal> is read only. You can
+ refer to it (if you have the <literal>SELECT</literal> privilege),
+ but not modify it. A column named with <literal>NEW</literal> can be
+ referred to if you have the <literal>SELECT</literal> privilege for
+ it. In a <literal>BEFORE</literal> trigger, you can also change its
+ value with <literal>SET NEW.<replaceable>col_name</replaceable> =
+ <replaceable>value</replaceable></literal> if you have the
+ <literal>UPDATE</literal> 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.
+ </para>
- <para>
- In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
- value for an <literal>AUTO_INCREMENT</literal> column is 0, not
- the automatically generated sequence number that will be generated
- when the new record actually is inserted.
- </para>
+ <para>
+ In a <literal>BEFORE</literal> trigger, the <literal>NEW</literal>
+ value for an <literal>AUTO_INCREMENT</literal> column is 0, not the
+ automatically generated sequence number that will be generated when
+ the new record actually is inserted.
+ </para>
- <para>
- <literal>OLD</literal> and <literal>NEW</literal> are MySQL
- extensions to triggers.
- </para>
+ <para>
+ <literal>OLD</literal> and <literal>NEW</literal> are MySQL
+ extensions to triggers.
+ </para>
- <remark role="todo">
- [js] Add an example demonstrating a multiple-table insert, since
- we now link to this section from a FAQ question concerning these.
- </remark>
+ <remark role="todo">
+ [js] Add an example demonstrating a multiple-table insert, since we
+ now link to this section from a FAQ question concerning these.
+ </remark>
- <para>
- By using the <literal>BEGIN ... END</literal> construct, you can
- define a trigger that executes multiple statements. Within the
- <literal>BEGIN</literal> block, you also can use other syntax that
- is allowed within stored routines such as conditionals and loops.
- However, just as for stored routines, if you use the
- <command>mysql</command> program to define a trigger that executes
- multiple statements, it is necessary to redefine the
- <command>mysql</command> statement delimiter so that you can use
- the <literal>;</literal> statement delimiter within the trigger
- definition. The following example illustrates these points. It
- defines an <literal>UPDATE</literal> 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
- <literal>BEFORE</literal> trigger because the value needs to be
- checked before it is used to update the row:
- </para>
+ <para>
+ By using the <literal>BEGIN ... END</literal> construct, you can
+ define a trigger that executes multiple statements. Within the
+ <literal>BEGIN</literal> block, you also can use other syntax that
+ is allowed within stored routines such as conditionals and loops.
+ However, just as for stored routines, if you use the
+ <command>mysql</command> program to define a trigger that executes
+ multiple statements, it is necessary to redefine the
+ <command>mysql</command> statement delimiter so that you can use the
+ <literal>;</literal> statement delimiter within the trigger
+ definition. The following example illustrates these points. It
+ defines an <literal>UPDATE</literal> 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
+ <literal>BEFORE</literal> trigger because the value needs to be
+ checked before it is used to update the row:
+ </para>
<programlisting>
mysql> <userinput>delimiter //</userinput>
@@ -322,96 +280,93 @@
mysql> <userinput>delimiter ;</userinput>
</programlisting>
- <para>
- It can be easier to define a stored procedure separately and then
- invoke it from the trigger using a simple <literal>CALL</literal>
- statement. This is also advantageous if you want to invoke the
- same routine from within several triggers.
- </para>
+ <para>
+ It can be easier to define a stored procedure separately and then
+ invoke it from the trigger using a simple <literal>CALL</literal>
+ statement. This is also advantageous if you want to invoke the same
+ routine from within several triggers.
+ </para>
- <remark role="todo">
- [MH] Let's get an example of this created when we have time.
- </remark>
+ <remark role="todo">
+ [MH] Let's get an example of this created when we have time.
+ </remark>
- <para>
- There are some limitations on what can appear in statements that a
- trigger executes when activated:
- </para>
+ <para>
+ There are some limitations on what can appear in statements that a
+ trigger executes when activated:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- The trigger cannot use the <literal>CALL</literal> statement
- to invoke stored procedures that return data to the client or
- that use dynamic SQL. (Stored procedures are allowed to return
- data to the trigger through <literal>OUT</literal> or
- <literal>INOUT</literal> parameters.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use the <literal>CALL</literal> statement to
+ invoke stored procedures that return data to the client or that
+ use dynamic SQL. (Stored procedures are allowed to return data
+ to the trigger through <literal>OUT</literal> or
+ <literal>INOUT</literal> parameters.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- The trigger cannot use statements that explicitly or
- implicitly begin or end a transaction such as <literal>START
- TRANSACTION</literal>, <literal>COMMIT</literal>, or
- <literal>ROLLBACK</literal>.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The trigger cannot use statements that explicitly or implicitly
+ begin or end a transaction such as <literal>START
+ TRANSACTION</literal>, <literal>COMMIT</literal>, or
+ <literal>ROLLBACK</literal>.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- MySQL handles errors during trigger execution as follows:
- </para>
+ <para>
+ MySQL handles errors during trigger execution as follows:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- If a <literal>BEFORE</literal> trigger fails, the operation on
- the corresponding row is not performed.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If a <literal>BEFORE</literal> trigger fails, the operation on
+ the corresponding row is not performed.
+ </para>
+ </listitem>
- <listitem>
- <para>
- A <literal>BEFORE</literal> trigger is activated by the
- <emphasis>attempt</emphasis> to insert or modify the row,
- regardless of whether the attempt subsequently succeeds.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ A <literal>BEFORE</literal> trigger is activated by the
+ <emphasis>attempt</emphasis> to insert or modify the row,
+ regardless of whether the attempt subsequently succeeds.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An <literal>AFTER</literal> trigger is executed only if the
- <literal>BEFORE</literal> trigger (if any) and the row
- operation both execute successfully.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An <literal>AFTER</literal> trigger is executed only if the
+ <literal>BEFORE</literal> trigger (if any) and the row operation
+ both execute successfully.
+ </para>
+ </listitem>
- <listitem>
- <para>
- An error during either a <literal>BEFORE</literal> or
- <literal>AFTER</literal> trigger results in failure of the
- entire statement that caused trigger invocation.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ An error during either a <literal>BEFORE</literal> or
+ <literal>AFTER</literal> trigger results in failure of the
+ entire statement that caused trigger invocation.
+ </para>
+ </listitem>
- <listitem>
- <para>
- For transactional tables, failure of a statement should cause
- rollback of all changes performed by the statement. Failure of
- a trigger causes the statement to fail, so trigger failure
- also causes rollback. 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.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ For transactional tables, failure of a statement should cause
+ rollback of all changes performed by the statement. Failure of a
+ trigger causes the statement to fail, so trigger failure also
+ causes rollback. 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.
+ </para>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
-
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11795 - in trunk: . refman-5.0 refman-5.1 refman-6.0 | paul.dubois | 12 Sep |
