Documentation Source Text

Check-in [b7dfcf7883]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhance the CREATE TRIGGER documentation to describe restrictions on INSERT, UPDATE, and DELETE statements that occur within triggers. CVS Ticket #3947.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b7dfcf78836257dc3344ec7937dff153531cc554
User & Date: drh 2009-07-03 15:38:49
Context
2009-07-29
06:32
Fix a typo in c3ref/intro.html. check-in: a3ee478f75 user: dan tags: trunk
2009-07-03
15:38
Enhance the CREATE TRIGGER documentation to describe restrictions on INSERT, UPDATE, and DELETE statements that occur within triggers. CVS Ticket #3947. check-in: b7dfcf7883 user: drh tags: trunk
2009-07-02
00:26
Add LLR to do with the advisory b-tree locks used in shared-cache mode. check-in: a4e2a17a94 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853


854
855




856






























857
858
859
860
861
862
863
....
1006
1007
1008
1009
1010
1011
1012















1013
1014
1015
1016
1017
1018
1019
....
2061
2062
2063
2064
2065
2066
2067








2068
2069
2070
2071
2072
2073
2074
....
2307
2308
2309
2310
2311
2312
2313

















2314
2315
2316
2317
2318
2319
2320
<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 [ON CONFLICT] clause may be specified as part of an [UPDATE] or [INSERT]
action within the body of the trigger.
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 [DROP TRIGGER | dropped]
when the table that they are 
associated with (the <i>table-name</i> table) is 
[DROP TABLE | dropped].  However if the the trigger actions reference
other tables, the trigger is not dropped or modified if those other
tables are [DROP TABLE | dropped] or [ALTER TABLE | modified].</p>

<p>Triggers are removed using the [DROP TRIGGER] statement.</p>




<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, 
................................................................................
the 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 match
the expression are removed.</p>
















<tcl>hd_fragment trucateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individual.
................................................................................
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>









<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

BubbleDiagram conflict-clause
</tcl>
................................................................................
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.</p>


















<p>If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option then the syntax of the UPDATE statement is extended
with optional ORDER BY and LIMIT clauses as follows:</p>

<tcl>BubbleDiagram update-stmt-limited</tcl>








|
|










>
>

<
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856

857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
....
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
....
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
....
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
<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 [ON CONFLICT] clause may be specified as part of an [UPDATE] or [INSERT]
action within the body of the trigger.
However if an [ON CONFLICT] 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 [DROP TRIGGER | dropped]
when the table that they are 
associated with (the <i>table-name</i> table) is 
[DROP TABLE | dropped].  However if the the trigger actions reference
other tables, the trigger is not dropped or modified if those other
tables are [DROP TABLE | dropped] or [ALTER TABLE | modified].</p>

<p>Triggers are removed using the [DROP TRIGGER] statement.</p>

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


<p>The [UPDATE], [DELETE], and [INSERT]
statements within triggers do not support
the full syntax for [UPDATE], [DELETE], and [INSERT] statements.  The following
restrictions apply:</p>

<ul>
<li><p>
  The name of the table to be modified in an [UPDATE], [DELETE], or [INSERT]
  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 [INSERT] statement
  is not supported.
  </p></li>

<li><p>
  The INDEXED BY and NOT INDEXED clauses are not supported for [UPDATE] and
  [DELETE] statements.
  </p></li>

<li><p>
  The ORDER BY and LIMIT clauses on [UPDATE] and [DELETE] statements are not
  supported.  ORDER BY and LIMIT are not normally supported for [UPDATE] or
  [DELETE] in any context but can be enabled for top-level statements
  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, 
................................................................................
the 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 match
the expression are removed.</p>

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>There are additional syntax restrictions on DELETE statements that
occur within the body of a [CREATE TRIGGER] statement.  The <i>table-name</i>
must be unqualified. 
In other words, the <i>database-name</i><b>.</b> prefix
on the table name is not allowed within triggers. 
The table from which to delete must be in the same
database as the table to which the trigger is attached.</p>

<p>The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE
statements within triggers.</p>

<p>The LIMIT clause (described below) is unsupported within triggers.</p>

<tcl>hd_fragment trucateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individual.
................................................................................
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<p>The optional "<i>database-name</i><b>.</b>" prefix on the <i>table-name</i>
is support for top-level INSERT statements only.  The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
Likely, the "DEFAULT VALUES" form of the INSERT statement is support for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

BubbleDiagram conflict-clause
</tcl>
................................................................................
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.</p>

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

<p>There are additional syntax restrictions on UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  The <i>table-name</i>
must be unqualified. 
In other words, the <i>database-name</i><b>.</b> prefix
on the table name is not allowed within triggers. 
The table to be modified must be in the same
database as the table to which the trigger is attached.</p>

<p>The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE
statements within triggers.</p>

<p>The LIMIT clause (described below) is unsupported within triggers.</p>

<h3>Optional LIMIT and ORDER BY Clauses</h3>

<p>If SQLite is built with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option then the syntax of the UPDATE statement is extended
with optional ORDER BY and LIMIT clauses as follows:</p>

<tcl>BubbleDiagram update-stmt-limited</tcl>