<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="trigger-syntax">
      </a>
      27.3.1 Trigger Syntax and Examples
     </h3>
    </div>
   </div>
  </div>
  <p>
   To create a trigger or drop a trigger, use the
   <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
    <code class="literal">
     CREATE TRIGGER
    </code>
   </a>
   or
   <a class="link" href="drop-trigger.html" title="15.1.34 DROP TRIGGER Statement">
    <code class="literal">
     DROP TRIGGER
    </code>
   </a>
   statement, described
      in
   <a class="xref" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
    Section 15.1.22, “CREATE TRIGGER Statement”
   </a>
   , and
   <a class="xref" href="drop-trigger.html" title="15.1.34 DROP TRIGGER Statement">
    Section 15.1.34, “DROP TRIGGER Statement”
   </a>
   .
  </p>
  <p>
   Here is a simple example that associates a trigger with a table,
      to activate for
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   operations.
      The trigger acts as an accumulator, summing the values inserted
      into one of the columns of the table.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47771600"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">account</span> <span class="token punctuation">(</span>acct_num <span class="token datatype">INT</span><span class="token punctuation">,</span> amount <span class="token datatype">DECIMAL</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> ins_sum <span class="token keyword">BEFORE</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token keyword">account</span>
       <span class="token keyword">FOR</span> <span class="token keyword">EACH</span> <span class="token keyword">ROW</span> <span class="token keyword">SET</span> <span class="token variable">@sum</span> <span class="token operator">=</span> <span class="token variable">@sum</span> <span class="token operator">+</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>amount<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
    <code class="literal">
     CREATE TRIGGER
    </code>
   </a>
   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 action time, the
      triggering event, and what to do when the trigger activates:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The keyword
      <code class="literal">
       BEFORE
      </code>
      indicates the trigger
          action time. In this case, the trigger activates before each
          row inserted into the table. The other permitted keyword here
          is
      <code class="literal">
       AFTER
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The keyword
      <code class="literal">
       INSERT
      </code>
      indicates the trigger
          event; that is, the type of operation that activates the
          trigger. In the example,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      operations cause trigger activation. You can also create
          triggers for
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      and
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      operations.
     </p>
    </li>
    <li class="listitem">
     <p>
      The statement following
      <code class="literal">
       FOR EACH ROW
      </code>
      defines the trigger body; that is, the statement to execute
          each time the trigger activates, which occurs once for each
          row affected by the triggering event. In the example, the
          trigger body is a simple
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
       </code>
      </a>
      that accumulates into a user variable 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">
       “
       <span class="quote">
        the value of the
        <code class="literal">
         amount
        </code>
        column to be
          inserted into the new row.
       </span>
       ”
      </span>
     </p>
    </li>
   </ul>
  </div>
  <p>
   To use the trigger, set the accumulator variable to zero, execute
      an
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement, and then see
      what value the variable has afterward:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66151861"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@sum</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token keyword">account</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">137</span><span class="token punctuation">,</span><span class="token number">14.98</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">141</span><span class="token punctuation">,</span><span class="token number">1937.50</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">97</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token number">100.00</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@sum</span> <span class="token keyword">AS</span> <span class="token string">'Total amount inserted'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Total amount inserted <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>               1852.48 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   In this case, the value of
   <code class="literal">
    @sum
   </code>
   after the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   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
   <a class="link" href="drop-trigger.html" title="15.1.34 DROP TRIGGER Statement">
    <code class="literal">
     DROP
      TRIGGER
    </code>
   </a>
   statement. You must specify the schema name if
      the trigger is not in the default schema:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55382018"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TRIGGER</span> test<span class="token punctuation">.</span>ins_sum<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you drop a table, any triggers for the table are also dropped.
  </p>
  <p>
   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.
  </p>
  <p>
   It is possible to define multiple triggers for a given table that
      have the same trigger event and action time. For example, you can
      have two
   <code class="literal">
    BEFORE UPDATE
   </code>
   triggers for a table. By
      default, triggers that have the same trigger event and action time
      activate in the order they were created. To affect trigger order,
      specify a clause after
   <code class="literal">
    FOR EACH ROW
   </code>
   that
      indicates
   <code class="literal">
    FOLLOWS
   </code>
   or
   <code class="literal">
    PRECEDES
   </code>
   and the name of an existing trigger
      that also has the same trigger event and action time. With
   <code class="literal">
    FOLLOWS
   </code>
   , the new trigger activates after the
      existing trigger. With
   <code class="literal">
    PRECEDES
   </code>
   , the new
      trigger activates before the existing trigger.
  </p>
  <p>
   For example, the following trigger definition defines another
   <code class="literal">
    BEFORE INSERT
   </code>
   trigger for the
   <code class="literal">
    account
   </code>
   table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14983965"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> ins_transaction <span class="token keyword">BEFORE</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token keyword">account</span>
       <span class="token keyword">FOR</span> <span class="token keyword">EACH</span> <span class="token keyword">ROW</span> <span class="token keyword">PRECEDES</span> ins_sum
       <span class="token keyword">SET</span>
       <span class="token variable">@deposits</span> <span class="token operator">=</span> <span class="token variable">@deposits</span> <span class="token operator">+</span> <span class="token function">IF</span><span class="token punctuation">(</span><span class="token keyword">NEW</span><span class="token punctuation">.</span>amount<span class="token operator">&gt;</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token keyword">NEW</span><span class="token punctuation">.</span>amount<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token variable">@withdrawals</span> <span class="token operator">=</span> <span class="token variable">@withdrawals</span> <span class="token operator">+</span> <span class="token function">IF</span><span class="token punctuation">(</span><span class="token keyword">NEW</span><span class="token punctuation">.</span>amount<span class="token operator">&lt;</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token operator">-</span><span class="token keyword">NEW</span><span class="token punctuation">.</span>amount<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span></code></pre>
  </div>
  <p>
   This trigger,
   <code class="literal">
    ins_transaction
   </code>
   , is similar to
   <code class="literal">
    ins_sum
   </code>
   but accumulates deposits and
      withdrawals separately. It has a
   <code class="literal">
    PRECEDES
   </code>
   clause that causes it to activate before
   <code class="literal">
    ins_sum
   </code>
   ; without that clause, it would activate
      after
   <code class="literal">
    ins_sum
   </code>
   because it is created after
   <code class="literal">
    ins_sum
   </code>
   .
  </p>
  <p>
   Within the trigger body, 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 MySQL extensions to triggers; they are
      not case-sensitive.
  </p>
  <p>
   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 (if you have the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege), but not modify it. You can refer to a column named
      with
   <code class="literal">
    NEW
   </code>
   if you have the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   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
   <a class="link" href="privileges-provided.html#priv_update">
    <code class="literal">
     UPDATE
    </code>
   </a>
   privilege for it. This means
      you can use a trigger to modify the values to be inserted into a
      new row or used to update a row. (Such a
   <code class="literal">
    SET
   </code>
   statement has no effect in an
   <code class="literal">
    AFTER
   </code>
   trigger
      because the row change has already occurred.)
  </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 sequence number that is generated automatically when the new
      row actually is inserted.
  </p>
  <p>
   By using the
   <a class="link" href="begin-end.html" title="15.6.1 BEGIN ... END Compound Statement">
    <code class="literal">
     BEGIN ...
      END
    </code>
   </a>
   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 permitted within stored
      routines such as conditionals and loops. However, just as for
      stored routines, if you use the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   program
      to define a trigger that executes multiple statements, it is
      necessary to redefine the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   statement
      delimiter so that you can use the
   <code class="literal">
    ;
   </code>
   statement
      delimiter 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
      must be checked before it is used to update the row:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44554125"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment" spellcheck="true">//</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> upd_check <span class="token keyword">BEFORE</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> <span class="token keyword">account</span>
       <span class="token keyword">FOR</span> <span class="token keyword">EACH</span> <span class="token keyword">ROW</span>
       <span class="token keyword">BEGIN</span>
           <span class="token keyword">IF</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>amount <span class="token operator">&lt;</span> <span class="token number">0</span> <span class="token keyword">THEN</span>
               <span class="token keyword">SET</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>amount <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
           <span class="token keyword">ELSEIF</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>amount <span class="token operator">&gt;</span> <span class="token number">100</span> <span class="token keyword">THEN</span>
               <span class="token keyword">SET</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>amount <span class="token operator">=</span> <span class="token number">100</span><span class="token punctuation">;</span>
           <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
       <span class="token keyword">END</span><span class="token punctuation">;</span><span class="token comment" spellcheck="true">//</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   It can be easier to define a stored procedure separately and then
      invoke it from the trigger using a simple
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   statement. This is also
      advantageous if you want to execute the same code from within
      several triggers.
  </p>
  <p>
   There are limitations on what can appear in statements that a
      trigger executes when activated:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The trigger cannot use the
      <a class="link" href="call.html" title="15.2.1 CALL Statement">
       <code class="literal">
        CALL
       </code>
      </a>
      statement to invoke stored procedures that return data to the
          client or that use dynamic SQL. (Stored procedures are
          permitted to return data to the trigger through
      <code class="literal">
       OUT
      </code>
      or
      <code class="literal">
       INOUT
      </code>
      parameters.)
     </p>
    </li>
    <li class="listitem">
     <p>
      The trigger cannot use statements that explicitly or
          implicitly begin or end a transaction, such as
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        START
          TRANSACTION
       </code>
      </a>
      ,
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      ,
          or
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        ROLLBACK
       </code>
      </a>
      .
          (
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        ROLLBACK to
          SAVEPOINT
       </code>
      </a>
      is permitted because it does not end a
          transaction.).
     </p>
    </li>
   </ul>
  </div>
  <p>
   See also
   <a class="xref" href="stored-program-restrictions.html" title="27.8 Restrictions on Stored Programs">
    Section 27.8, “Restrictions on Stored Programs”
   </a>
   .
  </p>
  <p>
   MySQL handles errors during trigger execution as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If a
      <code class="literal">
       BEFORE
      </code>
      trigger fails, the operation on
          the corresponding row is not performed.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       BEFORE
      </code>
      trigger is activated by the
      <span class="emphasis">
       <em>
        attempt
       </em>
      </span>
      to insert or modify the row,
          regardless of whether the attempt subsequently succeeds.
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <code class="literal">
       AFTER
      </code>
      trigger is executed only if any
      <code class="literal">
       BEFORE
      </code>
      triggers and the row operation
          execute successfully.
     </p>
    </li>
    <li class="listitem">
     <p>
      An error during either a
      <code class="literal">
       BEFORE
      </code>
      or
      <code class="literal">
       AFTER
      </code>
      trigger results in failure of the
          entire statement that caused trigger invocation.
     </p>
    </li>
    <li class="listitem">
     <p>
      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 nontransactional 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>
  <p>
   Triggers can contain direct references to tables by name, such as
      the trigger named
   <code class="literal">
    testref
   </code>
   shown in this
      example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11355583"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test1<span class="token punctuation">(</span>a1 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test2<span class="token punctuation">(</span>a2 <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test3<span class="token punctuation">(</span>a3 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test4<span class="token punctuation">(</span>
  a4 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  b4 <span class="token datatype">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">delimiter</span> <span class="token operator">|</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> testref <span class="token keyword">BEFORE</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> test1
  <span class="token keyword">FOR</span> <span class="token keyword">EACH</span> <span class="token keyword">ROW</span>
  <span class="token keyword">BEGIN</span>
    <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test2 <span class="token keyword">SET</span> a2 <span class="token operator">=</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>a1<span class="token punctuation">;</span>
    <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> test3 <span class="token keyword">WHERE</span> a3 <span class="token operator">=</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>a1<span class="token punctuation">;</span>
    <span class="token keyword">UPDATE</span> test4 <span class="token keyword">SET</span> b4 <span class="token operator">=</span> b4 <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">WHERE</span> a4 <span class="token operator">=</span> <span class="token keyword">NEW</span><span class="token punctuation">.</span>a1<span class="token punctuation">;</span>
  <span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token operator">|</span>

<span class="token keyword">delimiter</span> <span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test3 <span class="token punctuation">(</span>a3<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
  <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test4 <span class="token punctuation">(</span>a4<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
  <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that you insert the following values into table
   <code class="literal">
    test1
   </code>
   as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7642037"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1 <span class="token keyword">VALUES</span> 
       <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">8</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 8 rows affected (0.01 sec)</span>
<span class="token output">Records: 8  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   As a result, the four tables contain the following data:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26003281"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a1   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a2   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    7 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test3<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  9 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">5 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test4<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a4 <span class="token punctuation">|</span> b4   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>    3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  6 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  7 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  8 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  9 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 10 <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">10 rows in set (0.00 sec)</span></code></pre>
  </div>
 </div>
 <br/>
</div>
