bk commit - mysqldoc@docsrva tree (jon:1.3146)

[jon]

Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.3146 05/08/01 22:01:16 jon@stripped +2 -0
  Updated triggers chapter to
  show that trigger statements can reference
  tables by name as of 5.0.10.
  
  Sync refman-5.0.

  refman/triggers.xml
    1.13 05/08/01 22:01:14 jon@stripped +119 -16
    Trigger statements can now reference 
    tables by name (as of 5.0.10).

  refman-5.0/triggers.xml
    1.13 05/08/01 22:01:14 jon@stripped +118 -14
    Sync.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	jon
# Host:	gigan.
# Root:	/home/jon/bk/mysqldoc

--- 1.12/refman-5.0/triggers.xml	2005-08-01 01:50:01 +10:00
+++ 1.13/refman-5.0/triggers.xml	2005-08-01 22:01:14 +10:00
@@ -102,23 +102,118 @@
     </para>
 
     <para>
-      <emphasis role="bold">Note</emphasis>: Currently, triggers have
-      the same limitation as stored functions that they may not contain
-      direct references to tables by name. This limitation will be
-      lifted as soon as possible.
-    </para>
+      <emphasis role="bold">Note</emphasis>: 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 <literal>testref</literal> shown in this example:
+      
+<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
+);
 
-    <para>
-      <emphasis role="bold">Note</emphasis>: Currently, triggers are not
-      activated by cascaded foreign key actions. This limitation will be
-      lifted as soon as possible.
+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);
+</programlisting>
+
+      If you insert the following values into table 
+      <literal>test1</literal> as shown here:
+
+<programlisting>
+mysql&gt; INSERT INTO test1 VALUES 
+    -&gt; (1), (3), (1), (7), (1), (8), (4), (4);
+Query OK, 8 rows affected (0.01 sec)
+Records: 8  Duplicates: 0  Warnings: 0
+</programlisting>
+      
+      Then the data in the four tables will be as follows:
+
+<programlisting>
+mysql&gt; SELECT * FROM test1;
++------+
+| a1   |
++------+
+|    1 |
+|    3 |
+|    1 |
+|    7 |
+|    1 |
+|    8 |
+|    4 |
+|    4 |
++------+
+8 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test2;
++------+
+| a2   |
++------+
+|    1 |
+|    3 |
+|    1 |
+|    7 |
+|    1 |
+|    8 |
+|    4 |
+|    4 |
++------+
+8 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test3;
++----+
+| a3 |
++----+
+|  2 |
+|  5 |
+|  6 |
+|  9 |
+| 10 |
++----+
+5 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test4;
++----+------+
+| 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)
+</programlisting>
     </para>
 
     <para>
-      Although direct table references are not allowed in the triggered
-      statement, you can refer to columns in the table associated with
-      the trigger by using the names <literal>OLD</literal> and
-      <literal>NEW</literal>.
+      You can refer to columns in the table associated with the trigger 
+      by using the aliases <literal>OLD</literal> and 
+      <literal>NEW</literal>. 
       <literal>OLD.<replaceable>col_name</replaceable></literal>
refers
       to a column of a an existing row before it is updated or deleted.
       <literal>NEW.<replaceable>col_name</replaceable></literal>
refers
@@ -134,13 +229,22 @@
       NEW.<replaceable>col_name</replaceable></literal> requires the
       <literal>SELECT</literal> privilege on the column.
     </para>
+    
+    <para>
+      <emphasis role="bold">Note</emphasis>: Currently, triggers are not
+      activated by cascaded foreign key actions. This limitation will be
+      lifted as soon as possible.
+    </para>
 
     <para>
       The <literal>CREATE TRIGGER</literal> statement requires the
       <literal>SUPER</literal> privilege. It was added in MySQL 5.0.2.
     </para>
 
-<!--  TODO: Should need CREATE TRIGGER privilege, but there is no such thing yet.
-->
+<!--  
+      TODO: Should need CREATE TRIGGER privilege, but there is no such 
+      thing yet. 
+-->
 
   </section>
 

--- 1.12/refman/triggers.xml	2005-08-01 01:50:03 +10:00
+++ 1.13/refman/triggers.xml	2005-08-01 22:01:14 +10:00
@@ -102,23 +102,118 @@
     </para>
 
     <para>
-      <emphasis role="bold">Note</emphasis>: Currently, triggers have
-      the same limitation as stored functions that they may not contain
-      direct references to tables by name. This limitation will be
-      lifted as soon as possible.
-    </para>
-
-    <para>
-      <emphasis role="bold">Note</emphasis>: Currently, triggers are not
-      activated by cascaded foreign key actions. This limitation will be
-      lifted as soon as possible.
+      <emphasis role="bold">Note</emphasis>: 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 <literal>testref</literal> shown in this example:
+      
+<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);
+</programlisting>
+
+      If you insert the following values into table 
+      <literal>test1</literal> as shown here:
+
+<programlisting>
+mysql&gt; INSERT INTO test1 VALUES 
+    -&gt; (1), (3), (1), (7), (1), (8), (4), (4);
+Query OK, 8 rows affected (0.01 sec)
+Records: 8  Duplicates: 0  Warnings: 0
+</programlisting>
+      
+      Then the data in the four tables will be as follows:
+
+<programlisting>
+mysql&gt; SELECT * FROM test1;
++------+
+| a1   |
++------+
+|    1 |
+|    3 |
+|    1 |
+|    7 |
+|    1 |
+|    8 |
+|    4 |
+|    4 |
++------+
+8 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test2;
++------+
+| a2   |
++------+
+|    1 |
+|    3 |
+|    1 |
+|    7 |
+|    1 |
+|    8 |
+|    4 |
+|    4 |
++------+
+8 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test3;
++----+
+| a3 |
++----+
+|  2 |
+|  5 |
+|  6 |
+|  9 |
+| 10 |
++----+
+5 rows in set (0.00 sec)
+
+mysql&gt; SELECT * FROM test4;
++----+------+
+| 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)
+</programlisting>
     </para>
 
     <para>
-      Although direct table references are not allowed in the triggered
-      statement, you can refer to columns in the table associated with
-      the trigger by using the names <literal>OLD</literal> and
-      <literal>NEW</literal>.
+      You can refer to columns in the table associated with the trigger 
+      by using the aliases <literal>OLD</literal> and 
+      <literal>NEW</literal>. 
       <literal>OLD.<replaceable>col_name</replaceable></literal>
refers
       to a column of a an existing row before it is updated or deleted.
       <literal>NEW.<replaceable>col_name</replaceable></literal>
refers
@@ -134,14 +229,22 @@
       NEW.<replaceable>col_name</replaceable></literal> requires the
       <literal>SELECT</literal> privilege on the column.
     </para>
+    
+    <para>
+      <emphasis role="bold">Note</emphasis>: Currently, triggers are not
+      activated by cascaded foreign key actions. This limitation will be
+      lifted as soon as possible.
+    </para>
 
     <para>
       The <literal>CREATE TRIGGER</literal> statement requires the
       <literal>SUPER</literal> privilege. It was added in MySQL 5.0.2.
     </para>
 
-<!--  TODO: Should need CREATE TRIGGER privilege, but there is no such thing yet.
-->
-
+<!--  
+      TODO: Should need CREATE TRIGGER privilege, but there is no such 
+      thing yet. 
+-->
   </section>
 
   <section id="drop-trigger">