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 triggeris 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><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 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 theassociated 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 handlingpolicy 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 specifyingINSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETEor ON UPDATE triggers are defined on a view, then it is not an error to executean 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, andthat order records are stored in the "orders" table, the following triggerensures that all associated orders are redirected when a customer changeshis or her address:</p>}Example {CREATE TRIGGER update_customer_address UPDATE OF address ON customers   BEGIN    UPDATE orders SET address = new.address WHERE customer_name = old.name;  END;}puts {<p>With this trigger installed, executing the statement:</p>}Example {UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';}puts {<p>causes the following to be automatically executed:</p>}Example {UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';}puts {<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>}puts {<p>A special SQL function RAISE() may be used within a trigger-program, with the following syntax</p> }Syntax {raise-function} {RAISE ( ABORT, <error-message> ) | RAISE ( FAIL, <error-message> ) | RAISE ( ROLLBACK, <error-message> ) | RAISE ( IGNORE )}puts {<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="#droptrigger">DROP TRIGGER</a>statement.</p>}Section {CREATE VIEW} {createview}Syntax {sql-command} {CREATE [TEMP | TEMPORARY] VIEW [<database-name>.] <view-name> AS <select-statement>}puts {<p>The CREATE VIEW command assigns a name to a pre-packaged <a href="#select">SELECT</a>statement.  Once the view is created, it can be used in the FROM clauseof another SELECT in place of a table name.</p><p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"and "VIEW" then the view that is created is only visible to theprocess that opened the database and is automatically deleted whenthe database is closed.</p><p> If a &lt;database-name&gt; is specified, then the view is created in the named database. It is an error to specify both a &lt;database-name&gt;and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If nodatabase name is specified, and the TEMP keyword is not present,the table is created in the main database.</p><p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only in SQLite.  However, in many cases you can use a <a href="#createtrigger">TRIGGER</a> on the view to accomplish the same thing.  Views are removed with the <a href="#dropview">DROP VIEW</a> command.</p>}Section DELETE deleteSyntax {sql-statement} {DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]}puts {<p>The DELETE command is used to remove records from a table.The command consists of the "DELETE FROM" keywords followed bythe name of the table from which records are to be removed.</p><p>Without a WHERE clause, all rows of the table are removed.If a WHERE clause is supplied, then only those rows that matchthe expression are removed.</p>}Section {DETACH DATABASE} detachSyntax {sql-command} {DETACH [DATABASE] <database-name>}puts {<p>This statement detaches an additional database connection previously attached using the <a href="#attach">ATTACH DATABASE</a> statement.  Itis possible to have the same database file attached multiple times using different names, and detaching one connection to a file will leave the others intact.</p><p>This statement will fail if SQLite is in the middle of a transaction.</p>}Section {DROP INDEX} dropindexSyntax {sql-command} {DROP INDEX [IF EXISTS] [<database-name> .] <index-name>}puts {<p>The DROP INDEX statement removes an index addedwith the <a href="#createindex">CREATE INDEX</a> statement.  The index named is completely removed fromthe disk.  The only way to recover the index is to reenter theappropriate CREATE INDEX command.</p><p>The DROP INDEX statement does not reduce the size of the database file in the default mode.Empty space in the database is retained for later INSERTs.  To remove free space in the database, use the <a href="#vacuum">VACUUM</a> command.  If AUTOVACUUM mode is enabled for a database then spacewill be freed automatically by DROP INDEX.</p>}Section {DROP TABLE} droptableSyntax {sql-command} {DROP TABLE [IF EXISTS] [<database-name>.] <table-name>}puts {<p>The DROP TABLE statement removes a table added with the <a href="#createtable">CREATE TABLE</a> statement.  The name specified is thetable name.  It is completely removed from the database schema and the disk file.  The table can not be recovered.  All indices associated with the table are also deleted.</p><p>The DROP TABLE statement does not reduce the size of the database file in the default mode.  Empty space in the database is retained forlater INSERTs.  To remove free space in the database, use the <a href="#vacuum">VACUUM</a> command.  If AUTOVACUUM mode is enabled for a database then spacewill be freed automatically by DROP TABLE.</p><p>The optional IF EXISTS clause suppresses the error that would normallyresult if the table does not exist.</p>}Section {DROP TRIGGER} droptriggerSyntax {sql-statement} {DROP TRIGGER [<database-name> .] <trigger-name>}puts { <p>The DROP TRIGGER statement removes a trigger created by the <a href="#createtrigger">CREATE TRIGGER</a> statement.  The trigger is deleted from the database schema. Note that triggers are automatically dropped when the associated table is dropped.</p>}Section {DROP VIEW} dropviewSyntax {sql-command} {DROP VIEW <view-name>}puts {<p>The DROP VIEW statement removes a view created by the <a href="#createview">CREATE VIEW</a> statement.  The name specified is the view name.  It is removed from the database schema, but no actual data in the underlying base tables is modified.</p>}Section EXPLAIN explainSyntax {sql-statement} {EXPLAIN <sql-statement>}puts {<p>The EXPLAIN command modifier is a non-standard extension.  Theidea comes from a similar command found in PostgreSQL, but the operationis completely different.</p><p>If the EXPLAIN keyword appears before any other SQLite SQL commandthen instead of actually executing the command, the SQLite library willreport back the sequence of virtual machine instructions it would haveused to execute the command had the EXPLAIN keyword not been present.For additional information about virtual machine instructions seethe <a href="arch.html">architecture description</a> or the documentationon <a href="opcode.html">available opcodes</a> for the virtual machine.</p>}Section expression exprSyntax {expr} {<expr> <binary-op> <expr> |<expr> [NOT] <like-op> <expr> [ESCAPE <expr>] |<unary-op> <expr> |( <expr> ) |<column-name> |<table-name> . <column-name> |<database-name> . <table-name> . <column-name> |<literal-value> |<parameter> |<function-name> ( <expr-list> | STAR ) |<expr> ISNULL |<expr> NOTNULL |<expr> [NOT] BETWEEN <expr> AND <expr> |<expr> [NOT] IN ( <value-list> ) |<expr> [NOT] IN ( <select-statement> ) |<expr> [NOT] IN [<database-name> .] <table-name> |[EXISTS] ( <select-statement> ) |CASE [<expr>] LP WHEN <expr> THEN <expr> RPPLUS [ELSE <expr>] END |CAST ( <expr> AS <type> )} {like-op} {LIKE | GLOB | REGEXP}puts {<p>This section is different from the others.  Most other sections ofthis document talks about a particular SQL command.  This section doesnot talk about a standalone command but about "expressions" which are subcomponents of most other commands.</p><p>SQLite understands the following binary operators, in order fromhighest to lowest precedence:</p><blockquote><pre><font color="#2c2cf0"><big>||*    /    %+    -&lt;&lt;   &gt;&gt;   &amp;    |&lt;    &lt;=   &gt;    &gt;==    ==   !=   &lt;&gt;   </big>INAND   OR</font></pre></blockquote><p>Supported unary operators are these:</p><blockquote><pre><font color="#2c2cf0"><big>-    +    !    ~    NOT</big></font></pre></blockquote><p>Note that there are two variations of the equals and not equalsoperators.  Equals can be either}puts "[Operator =] or [Operator ==].The non-equals operator can be either[Operator !=] or [Operator {&lt;&gt;}].The [Operator ||] operator is \"concatenate\" - it joins togetherthe two strings of its operands.The operator [Operator %] outputs the remainder of its left operand modulo its right operand.</p><p>The result of any binary operator is a numeric value, exceptfor the [Operator ||] concatenation operator which gives a stringresult.</p>"puts {<a name="literal_value"></a><p>