System.Data.SQLite
Artifact Content
Not logged in

Artifact 1579bf96f98b09f3d4bf288f38465cce2eeeb17e:


<html dir="LTR" xmlns="http://www.w3.org/1999/xhtml" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:MSHelp="http://msdn.microsoft.com/mshelp" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:ndoc="urn:ndoc-preprocess">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=Windows-1252" />
    <title>CREATE TABLE</title>
    <link rel="stylesheet" type="text/css" href="../Include/ndoc.css" />
  </head>
  <body>
    <div id="header">
      <table width="100%" id="topTable">
        <tr id="headerTableRow1">
          <td align="left">
            <span id="runningHeaderText">CREATE TRIGGER</span>&nbsp;</td>
        </tr>
        <tr id="headerTableRow2">
          <td align="left">
            <span id="nsrTitle">SQLite Language Reference Documentation</span>
          </td>
        </tr>
        <tr id="headerTableRow3" style="display:none">
          <td>
            <a id="seeAlsoSectionLink" href="#seeAlsoToggle" onclick="OpenSection(seeAlsoToggle)">See Also</a>
            <a id="exampleSectionLink" href="#codeExampleToggle" onclick="OpenSection(codeExampleToggle)">Example</a>
          </td>
        </tr>
     </table>
      <table width="100%" id="bottomTable" cellspacing="0" cellpadding="0" style="display:none">
        <tr>
          <td>
            <span onclick="ExpandCollapseAll(toggleAllImage)" style="cursor:default;" onkeypress="ExpandCollapseAll_CheckKey(toggleAllImage)" tabindex="0">
              <img ID="toggleAllImage" class="toggleAll" src="collall.gif" />
              <label id="collapseAllLabel" for="toggleAllImage" style="display: none;">
							Collapse All
						</label>
              <label id="expandAllLabel" for="toggleAllImage" style="display: none;">
							Expand All
						</label>
            </span>
          </td>
        </tr>
      </table>
    </div>
    <div id="mainSection">
    <div id="mainBody">
      <h1 class="heading">
        SQL As Understood By SQLite</h1>
      <h4>
        CREATE TRIGGER</h4>
      <p>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">sql-statement</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b>
              <big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0">
                TRIGGER </font></b><i><font color="#ff3434">trigger-name</font></i><b><font color="#2c2cf0">
                </font></b>[<b><font color="#2c2cf0"> BEFORE </font></b><big>|</big><b><font color="#2c2cf0">
                  AFTER </font></b>]<b><font color="#2c2cf0"><br />
                  </font></b><i><font color="#ff3434">database-event</font></i><b><font color="#2c2cf0">
                    ON </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font
                      color="#2c2cf0"> <big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b>
              <i><font color="#ff3434">table-name</font></i><b><font color="#2c2cf0"><br />
              </font></b><i><font color="#ff3434">trigger-action</font></i><b><font color="#2c2cf0"></font></b></td>
          </tr>
        </table>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">sql-statement</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b>
              <big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0">
                TRIGGER </font></b><i><font color="#ff3434">trigger-name</font></i><b><font color="#2c2cf0">
                  INSTEAD OF<br />
                </font></b><i><font color="#ff3434">database-event</font></i><b><font color="#2c2cf0">
                  ON </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font
                    color="#2c2cf0"> <big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b>
              <i><font color="#ff3434">view-name</font></i><b><font color="#2c2cf0"><br />
              </font></b><i><font color="#ff3434">trigger-action</font></i><b><font color="#2c2cf0"></font></b></td>
          </tr>
        </table>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">database-event</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0">DELETE </font></b><big>|</big><b><font color="#2c2cf0">
                <br />
                INSERT </font></b><big>|</big><b><font color="#2c2cf0">
                  <br />
                  UPDATE </font></b><big>|</big><b><font color="#2c2cf0">
                    <br />
                    UPDATE OF </font></b><i><font color="#ff3434">column-list</font></i><b><font color="#2c2cf0"></font></b></td>
          </tr>
        </table>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">trigger-action</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0"></font></b>[<b><font color="#2c2cf0"> FOR EACH ROW </font>
              </b><big>|</big><b><font color="#2c2cf0"> FOR EACH STATEMENT </font></b>]<b><font
                color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"> WHEN </font></b><i><font color="#ff3434">
                  expression</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0">
                    <br />
                    BEGIN
                    <br />
                    &nbsp; &nbsp; </font></b><i><font color="#ff3434">trigger-step</font></i><b><font
                      color="#2c2cf0"> ; </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">
                        trigger-step</font></i><b><font color="#2c2cf0"> ; </font></b>]<b><font color="#2c2cf0"></font></b><big>*</big><b><font
                          color="#2c2cf0"><br />
                          END</font></b></td>
          </tr>
        </table>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">trigger-step</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0"></font></b><i><font color="#ff3434">update-statement</font></i><b><font
                color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"> </font></b><i><font
                  color="#ff3434">insert-statement</font></i><b><font color="#2c2cf0"> </font></b>
              <big>|</big><b><font color="#2c2cf0">
                <br />
              </font></b><i><font color="#ff3434">delete-statement</font></i><b><font color="#2c2cf0">
              </font></b><big>|</big><b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">
                select-statement</font></i><b><font color="#2c2cf0"></font></b></td>
          </tr>
        </table>
      </p>
      <p>
        The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers
        are database operations (the <i>trigger-action</i>) that are automatically performed
        when a specified database event (the <i>database-event</i>) occurs.
      </p>
      <p>
        A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular
        database table occurs, or whenever an UPDATE of one or more specified columns of
        a table are updated.</p>
      <p>
        At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT
        triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies
        that the SQL statements specified as <i>trigger-steps</i> may be executed (depending
        on the WHEN clause) for each database row being inserted, updated or deleted by
        the statement causing the trigger to fire.</p>
      <p>
        Both the WHEN clause and the <i>trigger-steps</i> may access elements of the row
        being inserted, deleted or updated using references of the form "NEW.<i>column-name</i>"
        and "OLD.<i>column-name</i>", where <i>column-name</i> is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be
        used in triggers on <i>trigger-event</i>s for which they are relevant, as follows:</p>
      <p>
        <table border="0" cellpadding="10">
          <tr>
            <td align="right" valign="top" width="120">
              <i>INSERT</i></td>
            <td valign="top">
              NEW references are valid</td>
          </tr>
          <tr>
            <td align="right" valign="top" width="120">
              <i>UPDATE</i></td>
            <td valign="top">
              NEW and OLD references are valid</td>
          </tr>
          <tr>
            <td align="right" valign="top" width="120">
              <i>DELETE</i></td>
            <td valign="top">
              OLD references are valid</td>
          </tr>
        </table>
      </p>
      <p>
      </p>
      <p>
        If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i>
        are only executed for
        rows for which the WHEN clause is true. If no WHEN clause
        is supplied, the SQL statements are executed for all rows.</p>
      <p>
        The specified <i>trigger-time</i> determines when the <i>trigger-steps</i> will
        be executed relative to the insertion, modification or removal of the associated
        row.</p>
      <p>
        An ON CONFLICT clause may be specified as part of an UPDATE or INSERT <i>trigger-step</i>.
        However if an ON CONFLICT clause is specified as part of the statement causing the
        trigger to fire, then this conflict handling policy is used instead.</p>
      <p>
        Triggers are automatically dropped when the table that they are associated with
        is dropped.</p>
      <p>
        Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD
        OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE
        triggers are defined on a view, then it is not an error to execute an INSERT, DELETE
        or UPDATE statement on the view, respectively. Thereafter, executing an INSERT,
        DELETE or UPDATE on the view causes the associated triggers to fire. The real tables
        underlying the view are not modified (except possibly explicitly, by a trigger program).</p>
      <p>
        <b>Example:</b></p>
      <p>
        Assuming that customer records are stored in the "customers" table, and that order
        records are stored in the "orders" table, the following trigger ensures that all
        associated orders are redirected when a customer changes his or her address:</p>
      <blockquote>
        <pre>
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
</pre>
      </blockquote>
      <p>
        With this trigger installed, executing the statement:</p>
      <blockquote>
        <pre>
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
</pre>
      </blockquote>
      <p>
        causes the following to be automatically executed:</p>
      <blockquote>
        <pre>
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
</pre>
      </blockquote>
      <p>
        Note that currently, triggers may behave oddly when created on tables with INTEGER
        PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY
        field of a row that will be subsequently updated by the statement that causes the
        trigger to fire, then the update may not occur. The workaround is to declare the
        table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.</p>
      <p>
        A special SQL function RAISE() may be used within a trigger-program, with the following
        syntax</p>
      <p>
        <table cellpadding="10">
          <tr>
            <td align="right" width="1%" nowrap>
              <i><font color="#ff3434">raise-function</font></i> ::=</td>
            <td>
              <b><font color="#2c2cf0">RAISE <big>(</big> ABORT<big>,</big> </font></b><i><font
                color="#ff3434">error-message</font></i><b><font color="#2c2cf0"> <big>)</big> </font>
                </b><big>|</big><b><font color="#2c2cf0">
                  <br />
                  RAISE <big>(</big> FAIL<big>,</big> </font></b><i><font color="#ff3434">error-message</font></i><b><font
                    color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0">
                      <br />
                      RAISE <big>(</big> ROLLBACK<big>,</big> </font></b><i><font color="#ff3434">error-message</font></i><b><font
                        color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0">
                          <br />
                          RAISE <big>(</big> IGNORE <big>)</big></font></b></td>
          </tr>
        </table>
      </p>
      <p>
        When one of the first three forms is called during trigger-program execution, the
        specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and
        the current query terminates. An error code of SQLITE_CONSTRAINT is returned to
        the user, along with the specified error message.</p>
      <p>
        When RAISE(IGNORE) is called, the remainder of the current trigger program, the
        statement that caused the trigger program to execute and any subsequent trigger
        programs that would of been executed are abandoned. No database changes are rolled
        back. If the statement that caused the trigger program to execute is itself part
        of a trigger program, then that trigger program resumes execution at the beginning
        of the next step.
      </p>
      <p>
        Triggers are removed using the <a href="lang_droptrigger.html">DROP TRIGGER</a>
        statement.</p>
      <p>
      <hr>
        &nbsp;</p>
      <div id="footer">
        <p>
          &nbsp;</p>
        <p>
        </p>
      </div>
    </div>
    </div>
  </body>
</html>