BlitzNG
/
database.mod
mirror of https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: CREATE TRIGGER</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>CREATE TRIGGER</h1><h4><a href="syntaxdiagrams.html#create-trigger-stmt">create-trigger-stmt:</a></h4><blockquote> <img alt="syntax diagram create-trigger-stmt" src="images/syntax/create-trigger-stmt.gif"></img> </blockquote>


<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations 
that are automatically performed when a specified database event
occurs.  </p>

<p>A trigger may be specified to fire whenever a <a href="lang_delete.html">DELETE</a>, <a href="lang_insert.html">INSERT</a>,
or <a href="lang_update.html">UPDATE</a> of a
particular database table occurs, or whenever an <a href="lang_update.html">UPDATE</a> occurs on
on one or more specified columns of a table.</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 in the trigger
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 trigger actions 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
events for which they are relevant, as follows:</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120><i>INSERT</i></td>
<td valign="top">NEW references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>UPDATE</i></td>
<td valign="top">NEW and OLD references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>DELETE</i></td>
<td valign="top">OLD references are valid</td>
</tr>
</table>
</p>

<p>If a WHEN clause is supplied, the SQL statements specified
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 BEFORE or AFTER keyword determines when the trigger actions
will be executed relative to the insertion, modification or removal of the
associated row.</p>

<p>An <a href="lang_conflict.html">ON CONFLICT</a> clause may be specified as part of an <a href="lang_update.html">UPDATE</a> or <a href="lang_insert.html">INSERT</a>
action within the body of the trigger.
However if an <a href="lang_conflict.html">ON CONFLICT</a> clause is specified as part of 
the statement causing the trigger to fire, then conflict handling
policy of the outer statement is used instead.</p>

<p>Triggers are automatically <a href="lang_droptrigger.html">dropped</a>
when the table that they are 
associated with (the <i>table-name</i> table) is 
<a href="lang_droptable.html">dropped</a>.  However if the the trigger actions reference
other tables, the trigger is not dropped or modified if those other
tables are <a href="lang_droptable.html">dropped</a> or <a href="lang_altertable.html">modified</a>.</p>

<p>Triggers are removed using the <a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>

<h3>Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
    Triggers</h3>

<p>The <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and <a href="lang_insert.html">INSERT</a>
statements within triggers do not support
the full syntax for <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and <a href="lang_insert.html">INSERT</a> statements.  The following
restrictions apply:</p>

<ul>
<li><p>
  The name of the table to be modified in an <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, or <a href="lang_insert.html">INSERT</a>
  statement must be an unqualified table name.  In other words, one must
  use just "<i>tablename</i>" not "<i>database</i><b>.</b><i>tablename</i>"
  when specifying the table.  The table to be modified must exist in the
  same database as the table or view to which the trigger is attached.
  </p></li>

<li><p>
  The "INSERT INTO <i>table</i> DEFAULT VALUES" form of the <a href="lang_insert.html">INSERT</a> statement
  is not supported.
  </p></li>

<li><p>
  The INDEXED BY and NOT INDEXED clauses are not supported for <a href="lang_update.html">UPDATE</a> and
  <a href="lang_delete.html">DELETE</a> statements.
  </p></li>

<li><p>
  The ORDER BY and LIMIT clauses on <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements are not
  supported.  ORDER BY and LIMIT are not normally supported for <a href="lang_update.html">UPDATE</a> or
  <a href="lang_delete.html">DELETE</a> in any context but can be enabled for top-level statements
  using the <a href="compile.html#enable_update_delete_limit">SQLITE_ENABLE_UPDATE_DELETE_LIMIT</a> compile-time option.  However,
  that compile-time option only applies to top-level <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a>
  statements, not <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements within triggers.
  </p></li>
</ul>

<a name="instead_of_trigger"></a>

<h3>INSTEAD OF trigger</h3>

<p>Triggers may be created on <a href="lang_createview.html">views</a>, 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>

<h3>Examples</h3>

<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>

<a name="undef_before"></a>

<h3>Cautions On The Use Of BEFORE triggers</h3>

<p>If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
that was to have been updated or deleted, then the result of the subsequent
update or delete operation is undefined.  Furthermore, if a BEFORE trigger
modifies or deletes a row, then it is undefined whether or not AFTER triggers
that would have otherwise run on those rows will in fact run.
</p>

<p>The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which
the rowid is not explicitly set to an integer.</p>

<p>Because of the behaviors described above, programmers are encouraged to
prefer AFTER triggers over BEFORE triggers.</p>

<h3>The RAISE() function</h3>

<p>A special SQL function RAISE() may be used within a trigger-program,
with the following syntax</p> 

<h4><a href="syntaxdiagrams.html#raise-function">raise-function:</a></h4><blockquote> <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif"></img> </blockquote>


<p>When one of the first three forms is called during trigger-program
execution, the specified <a href="lang_conflict.html">ON CONFLICT</a> processing is performed
(either ABORT, FAIL or ROLLBACK) and the current query terminates.
An error code of SQLITE_CONSTRAINT is returned to the application,
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>