Documentation Source Text

  using the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option.  However,
  that compile-time option only applies to top-level [UPDATE] and [DELETE]
  statements, not [UPDATE] and [DELETE] statements within triggers.)^
  </p></li>
</ul>

<tcl>hd_fragment instead_of_trigger {INSTEAD OF} {INSTEAD OF trigger}</tcl>
<h3>INSTEAD OF trigger</h3>

<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.  ^Instead,
................................................................................
triggers to fire. ^The real tables underlying the view are not modified
(except possibly explicitly, by a trigger program).</p>

<p>^Note that the [sqlite3_changes()] and [sqlite3_total_changes()] interfaces
do not count INSTEAD OF trigger firings, but the
[count_changes pragma] does count INSTEAD OF trigger firing.</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>

<tcl>Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
................................................................................
}</tcl>

<p>causes the following to be automatically executed:</p>

<tcl>Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}</tcl>)^






























<tcl>hd_fragment undef_before {undefined BEFORE trigger behavior}</tcl>
<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

  using the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option.  However,
  that compile-time option only applies to top-level [UPDATE] and [DELETE]
  statements, not [UPDATE] and [DELETE] statements within triggers.)^
  </p></li>
</ul>

<tcl>hd_fragment instead_of_trigger {INSTEAD OF} {INSTEAD OF trigger}</tcl>
<h3>INSTEAD OF triggers</h3>

<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.  ^Instead,
................................................................................
triggers to fire. ^The real tables underlying the view are not modified
(except possibly explicitly, by a trigger program).</p>

<p>^Note that the [sqlite3_changes()] and [sqlite3_total_changes()] interfaces
do not count INSTEAD OF trigger firings, but the
[count_changes pragma] does count INSTEAD OF trigger firing.</p>

<h3>Some Example Triggers</h3>

<p>^(Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following
UPDATE trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>

<tcl>Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
................................................................................
}</tcl>

<p>causes the following to be automatically executed:</p>

<tcl>Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}</tcl>)^

<p>For an example of an INSTEAD OF trigger, consider the following schema:

<tcl>Example {
CREATE TABLE customer(
  cust_id INTEGER PRIMARY KEY,
  cust_name TEXT,
  cust_addr TEXT
);
CREATE VIEW customer_address AS
   SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
BEGIN
  UPDATE customer SET cust_addr=NEW.cust_addr
   WHERE cust_id=NEW.cust_id;
END;
}</tcl>

<p>With the schema above, a statement of the form:</p>

<tcl>Example {
UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_id;
}</tcl>

<p>Causes the customer.cust_addr field to be updated for a specific
customer entry that has customer.cust_id equal to the $cust_id parameter.
Note how the values assigned to the view are made available as field
in the special "NEW" table within the trigger body.</p>

<tcl>hd_fragment undef_before {undefined BEFORE trigger behavior}</tcl>
<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