syntax.html

sqlite的帮助文档

  all triggers attached to the view identified by <i>fullname</i>
  to be removed from their database and discarded.
</b></blockquote>

<h3>3.7 CREATE TRIGGER</h3>

<a name="H43300"></a>
<blockquote><b>H43300:</b>
  The SQLite parser shall accept CREATE TRIGGER statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= CREATE temp TRIGGER ifnotexists fullname trigger trigger_body.
trigger ::= trigger_time trigger_event foreach_clause when_clause.
trigger_body ::= BEGIN trigger_cmd_list END.
trigger_cmd_list ::= trigger_cmd_list trigger_cmd SEMI.
trigger_cmd_list ::= trigger_cmd_list.
trigger_cmd ::= DELETE FROM tablename where.
trigger_cmd ::= update_cmd tablename SET setlist where.
trigger_cmd ::= insert_cmd INTO tablename columnlist_opt VALUES LP exprlist RP.
trigger_cmd ::= insert_cmd INTO tablename columnlist_opt select.
trigger_cmd ::= select.
trigger_event ::= DELETE ON trigger_target.
trigger_event ::= INSERT ON trigger_target.
trigger_event ::= UPDATE OF columnlist ON trigger_target.
trigger_event ::= UPDATE ON trigger_target.
trigger_target ::= fullname.
trigger_time ::= AFTER.
trigger_time ::= BEFORE.
trigger_time ::= INSTEAD OF.
trigger_time ::=.
foreach_clause ::= FOR EACH ROW.
foreach_clause ::=.
when_clause ::= WHEN expr.
when_clause ::=.
</pre></blockquote></b></blockquote><a name="H43303"></a>
<blockquote><b>H43303:</b>
  When the TEMP keyword appears in a CREATE TRIGGER statement and the
  <i>databasename</i> of the <i>fullname</i> exists
  then the preparation of the statement
  shall fail with an error.
</b></blockquote><a name="H43306"></a>
<blockquote><b>H43306:</b>
  When the TEMP keyword appears in a CREATE TRIGGER statement 
  the target database of the trigger shall be "temp".
</b></blockquote><a name="H43309"></a>
<blockquote><b>H43309:</b>
  When the TEMP keyword is omitted in a CREATE TRIGGER statement and the
  <i>databasename</i> of the <i>fullname</i> is
  omitted then the target database of the trigger shall be "main".
</b></blockquote><a name="H43312"></a>
<blockquote><b>H43312:</b>
  When the <i>databasename</i> of <i>fullname</i> in a CREATE TRIGGER
  statement exists, then the target database shall be the database
  named by <i>databasename</i>.
</b></blockquote><a name="H43315"></a>
<blockquote><b>H43315:</b>
  If a CREATE TRIGGER does not specify a <i>trigger_time</i> then
  the <i>trigger_time</i> shall be BEFORE.
</b></blockquote><a name="H43318"></a>
<p>
  An INSTEAD OF trigger may be used only on views.  Other kinds of triggers
  may be used on tables only.
</p><blockquote><b>H43318:</b>
  The preparation of a CREATE TRIGGER statement shall fail with an error
  the <i>trigger_time</i> is INSTEAD OF and <i>trigger_target</i> is not
  the name of view in the target database.
</b></blockquote><a name="H43321"></a>
<blockquote><b>H43321:</b>
  The preparation of a CREATE TRIGGER statement shall fail with an error
  the <i>trigger_time</i> is not INSTEAD OF and <i>trigger_target</i> is not
  the name of an ordinary table in the target database.
</b></blockquote><a name="H43324"></a>
<blockquote><b>H43324:</b>
  The preparation of a CREATE TRIGGER statement shall fail with an error
  if the <i>trigger_target</i> is a system table.
</b></blockquote>


<h3>3.8 DROP TRIGGER</h3>

<a name="H43500"></a>
<blockquote><b>H43500:</b>
  The SQLite parser shall accept DROP TRIGGER statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= DROP TRIGGER ifexists fullname.
</pre></blockquote></b></blockquote><a name="H43504"></a>
<blockquote><b>H43504:</b>
  The preparation of a DROP TRIGGER statement shall fail with an
  error if the statement lacks an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing trigger.
</b></blockquote><a name="H43507"></a>
<blockquote><b>H43507:</b>
  The evaluation of a DROP TRIGGER statement shall be a silent no-op
  if the the statement has an IF EXISTS clause and the
  <i>fullname</i> does not reference a existing trigger.
</b></blockquote><a name="H43511"></a>
<blockquote><b>H43511:</b>
  The successful evaluation of a DROP TRIGGER statement shall cause
  the trigger identified by <i>fullname</i> to be removed from its
  database and discarded.
</b></blockquote><a name="H43514"></a>
<blockquote><b>H43514:</b>
  The successful evaluation of a DROP TRIGGER statement shall cause
  all triggers attached to the trigger identified by <i>fullname</i>
  to be removed from their database and discarded.
</b></blockquote>

<h3>3.9 CREATE VIRTUAL TABLE</h3>

<a name="H43600"></a>
<blockquote><b>H43600:</b>
  The SQLite parser shall accept CREATE VIRTUAL TABLE statements
  that conform to the following syntax.
<blockquote><pre>
cmd ::= CREATE VIRTUAL TABLE fullname USING name vtab_arg_list.
vtab_arg_list ::= .
vtab_arg_list ::= LP vtab_arg_token RP.
vtab_arg_token ::= ANY.
vtab_arg_token ::= LP anylist RP.
anylist ::= .
anylist ::= anylist ANY.
</pre></blockquote></b></blockquote>

<h3>3.10 ALTER TABLE</h3>
<h4>3.11 ALTER TABLE RENAME</h4>

<a name="H43700"></a>
<blockquote><b>H43700:</b>
  The SQLite parser shall accept ALTER TABLE RENAME statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= ALTER TABLE fullname RENAME TO name.
</pre></blockquote></b></blockquote>

<h4>3.12 ALTER TABLE ADD COLUMN</h4>

<a name="H43750"></a>
<blockquote><b>H43750:</b>
  The SQLite parser shall accept ALTER TABLE ADD COLUMN statements
  that conform to the following syntax:
<blockquote><pre>
cmd ::= ALTER TABLE fullname ADD column_keyword column.
column_keyword ::= .
column_keyword ::= COLUMNKW.
</pre></blockquote></b></blockquote>

<h2>4.0 Data Manipulation Language (DML)</h2>

<h3>4.1 INSERT</h3>
<a name="H43810"></a>
<blockquote><b>H43810:</b>
  The SQLite parser shall accept INSERT statements that
  conform to the following syntax:
<blockquote><pre>
cmd ::= insert_cmd INTO fullname columnlist_opt insert_content.
insert_cmd ::= INSERT.
insert_cmd ::= REPLACE.
insert_cmd ::= INSERT OR REPLACE.
insert_cmd ::= INSERT OR IGNORE.
insert_cmd ::= INSERT OR ABORT.
insert_cmd ::= INSERT OR FAIL.
insert_cmd ::= INSERT OR ROLLBACK.
columnlist_opt ::= .
columnlist_opt ::= LP columnlist RP.
columnlist ::= columnname.
columnlist ::= columnlist COMMA columnname.
</pre></blockquote></b></blockquote><a name="H43813"></a>
<blockquote><b>H43813:</b>
  The preparation of an INSERT statement shall fail with an error
  if the <i>instcolist</i> contains a reference to a column which
  is not a column in the table identified by <i>fullname</i> and is
  not one of the special column named "ROWID", "OID", or "_ROWID_".
</b></blockquote><a name="H43816"></a>
<blockquote><b>H43816:</b>
  The preparation of an INSERT statement shall fail with an error
  if <i>fullname</i> does not identify either a view with an
  INSTEAD OF INSERT trigger or a table.
</b></blockquote><a name="H43819"></a>
<blockquote><b>H43819:</b>
  The preparation of an INSERT statement shall fail with an error
  if the <i>objectname</i> of the <i>fullname</i> is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable schema mode.
</b></blockquote><a name="H43821"></a>
<blockquote><b>H43821:</b>
  When the form of an INSERT statement is simply "INSERT" then the
  default null- and uniqueness-conflict resolution algorithms shall
  be used.
</b></blockquote><a name="H43824"></a>
<blockquote><b>H43824:</b>
  When the form of an INSERT statement is simply "REPLACE" then the
  null- and uniqueness-conflict resolution algorithms shall all
  change to REPLACE.
</b></blockquote><a name="H43827"></a>
<blockquote><b>H43827:</b>
  When the form of an INSERT statement is "INSERT OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.
</b></blockquote><a name="H43831"></a>
<blockquote><b>H43831:</b>
  Name resolution in the <i>insert_content</i> term of an INSERT statement
  shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the <i>fullname</i> table.
</b></blockquote>

<h4>4.1.1 INSERT VALUE</h4>
<a name="H43840"></a>
<blockquote><b>H43840:</b>
  The SQLite parser shall accept INSERT VALUE statements that
  conform to the following syntax:
<blockquote><pre>
insert_content ::= VALUES LP exprlist RP.
</pre></blockquote></b></blockquote><a name="H43843"></a>
<blockquote><b>H43843:</b>
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of entries in the <i>exprlist</i>.
</b></blockquote><a name="H43846"></a>
<blockquote><b>H43846:</b>
  The preparation of an INSERT VALUE statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  entries in the <i>exprlist</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
</b></blockquote>

<h4>4.1.2 INSERT SELECT</h4>
syntaxreq {H43870} {} {} {
  The SQLite parser shall accept INSERT SELECT statements that
  conform to the following syntax:
} {
  insert_contents ::= select.
}
syntaxreq {H43873} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element exists and the number of 
  entries in the <i>instcollist</i> is different
  from the number of columns in the result set of <i>select</i>.
}
syntaxreq {H43876} {} {} {
  The preparation of an INSERT SELECT statement shall fail with an
  error if the <i>columnlist</i> element does not exists and the number of 
  columns in the result set of the <i>select</i> is different
  from the number of columns in the table or view identified by
  <i>fullname</i>.
}

<h4>4.1.3 INSERT DEFAULT</h4>
<a name="H43890"></a>
<blockquote><b>H43890:</b>
  The SQLite parser shall accept INSERT DEFAULT statements that
  conform to the following syntax:
<blockquote><pre>
insert_contents ::= DEFAULT VALUES.
</pre></blockquote></b></blockquote>

<h3>4.2 DELETE</h3>

<a name="H43900"></a>
<blockquote><b>H43900:</b>
  The SQLite parser shall accept DELETE statements that
  conform to the following syntax:
<blockquote><pre>
cmd ::= DELETE FROM fullname where.
where ::= .
where ::= WHERE expr.
</pre></blockquote></b></blockquote><a name="H43904"></a>
<blockquote><b>H43904:</b>
  The preparation of a DELETE statement shall fail with an error if
  the <i>fullname</i> element does not identify a view with an
  INSTEAD OF DELETE trigger or a table.
</b></blockquote><a name="H43907"></a>
<blockquote><b>H43907:</b>
  The preparation of a DELETE statement shall fail with an error if
  the <i>objectname</i> of the <i>fullname</i> element is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable_schema mode.
</b></blockquote><a name="H43911"></a>
<blockquote><b>H43911:</b>
  Name resolution in the <i>where</i> term of a DELETE statement
  shall be carried out using a name context
  with an empty result set and a source set holding
  single source element that is the <i>fullname</i> table.
</b></blockquote>

<h3>4.3 UPDATE</h3>

<a name="H44100"></a>
<blockquote><b>H44100:</b>
  The SQLite parser shall accept UPDATE statements that
  conform to the following syntax:
<blockquote><pre>
cmd ::= update_cmd fullname SET setlist where.
update_cmd ::= UPDATE.
update_cmd ::= UPDATE OR IGNORE.
update_cmd ::= UPDATE OR REPLACE.
update_cmd ::= UPDATE OR ABORT.
update_cmd ::= UPDATE OR FAIL.
update_cmd ::= UPDATE OR ROLLBACK.
setlist ::= setting.
setlist ::= setlist COMMA setting.
setting ::= columnname EQ expr.
</pre></blockquote></b></blockquote><a name="H44113"></a>
<blockquote><b>H44113:</b>
  The preparation of an UPDATE statement shall fail with an error
  if any <i>columnname</i>
  is not a column in the table identified by <i>fullname</i> and is
  not one of the special columns named "ROWID", "OID", or "_ROWID_".
</b></blockquote><a name="H44116"></a>
<blockquote><b>H44116:</b>
  The preparation of an UPDATE statement shall fail with an error
  if <i>fullname</i> does not identify either a view with an
  INSTEAD OF UPDATE trigger that covers all <i>columnname</i>s or a table.
</b></blockquote><a name="H44119"></a>
<blockquote><b>H44119:</b>
  The preparation of an UPDATE statement shall fail with an error
  if the <i>objectname</i> of the <i>fullname</i> is "sqlite_master"
  or "sqlite_temp_master" and the database connection is not in
  writeable schema mode.
</b></blockquote><a name="H44121"></a>
<blockquote><b>H44121:</b>
  When the form of an UPDATE statement is simply "UPDATE" then the
  default null- and uniqueness-conflict resolution algorithms shall
  be used.
</b></blockquote><a name="H44127"></a>
<blockquote><b>H44127:</b>
  When the form of an UPDATE statement is "UPDATE OR <i>algorithm</i>" 
  then the null- and uniqueness-conflict resolution algorithms shall all
  change to <i>algorithm</i>.