Documentation Source Text

Check-in [1179f9425f]
Login

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

Overview
Comment:Update trigger documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:1179f9425f8822ea2d6def12a2d31e64c4813d53
User & Date: drh 2009-04-29 14:33:02
Context
2009-04-30
16:00
Update the DELETE documentation to discuss the truncation optimization. check-in: dcd3325dd3 user: drh tags: trunk
2009-04-29
14:33
Update trigger documentation. check-in: 1179f9425f user: drh tags: trunk
10:16
Fix a bug in the formula to compute the layout of index b-tree cells. check-in: 7e6a5768d9 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812

813
814
815
816

817
818



819



820



821
822
823
824
825
826
827
828
829
830




831
832
833
834
835
836
837
838
...
849
850
851
852
853
854
855


856
857
858
859
860
861
862


863
864
865
866
867
868
869
...
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

BubbleDiagram create-trigger-stmt 1
</tcl>

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

<p>A trigger may be specified to fire whenever a [DELETE], [INSERT],
or [UPDATE] of a
particular database table occurs, or whenever an [UPDATE] of one or more
specified columns of a table are updated.</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 as <i>trigger-steps</i> 
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 <i>trigger-steps</i> 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
<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>
................................................................................
<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 the
associated 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 handling
policy is used instead.</p>


<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>







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



<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. 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, 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 {
................................................................................
}</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>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>



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

<tcl>BubbleDiagram raise-function</tcl>

<p>When one of the first three forms is called during 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 [DROP TRIGGER] statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view views}

BubbleDiagram create-view-stmt 1
</tcl>







|
|
|








|



|




|







 







|
|



|




>
|



>
|
|
>
>
>

>
>
>

>
>
>










>
>
>
>
|







 







>
>







>
>







 







<
<







758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
...
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
...
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
...
895
896
897
898
899
900
901


902
903
904
905
906
907
908
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

BubbleDiagram create-trigger-stmt 1
</tcl>

<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 [DELETE], [INSERT],
or [UPDATE] of a
particular database table occurs, or whenever an [UPDATE] of one or more
specified columns of a table are updated.</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>
................................................................................
<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 [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, 
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>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 {
................................................................................
}</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>

<h3>Rowids and BEFORE triggers</h3>

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

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

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

<tcl>BubbleDiagram raise-function</tcl>

<p>When one of the first three forms is called during 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>




<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view views}

BubbleDiagram create-view-stmt 1
</tcl>

Changes to pages/pragma.in.

187
188
189
190
191
192
193
194
195




196
197
198
199
200
201
202
203
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers.</p>

    <p>The use of this pragma is discouraged.  A better way to get the
    row change count is to use the [sqlite3_changes()] or




    [sqlite3_total_changes()] interfaces.</p>

<tcl>Subsection default_cache_size</tcl>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>This pragma queries or sets the suggested maximum number of pages
    of disk cache that will be allocated per open database file.
    The difference between this pragma and [cache_size] is that the







|
|
>
>
>
>
|







187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers.</p>

    <p>Another way to get the row change counts is to use the
    [sqlite3_changes()] or [sqlite3_total_changes()] interfaces.
    There is a subtle different, though.  When an INSERT, UPDATE, or
    DELETE is run against a view using an [INSTEAD OF trigger],
    the count_changes pragma reports the number of rows in the view
    that fired the trigger, whereas [sqlite3_changes()] and
    [sqlite3_total_changes()] do not.</p>

<tcl>Subsection default_cache_size</tcl>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>This pragma queries or sets the suggested maximum number of pages
    of disk cache that will be allocated per open database file.
    The difference between this pragma and [cache_size] is that the

Changes to pages/sitemap.in.

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
     </ul></li>
<li> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> 
     <ul>
     <li> <a href="http://www.sqlite.org/cvstrac/timeline">Timeline</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/wiki">Wiki</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/tktnew">Report a bug</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/rptview?rn=5">Browse
            bug beports</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/dir?d=sqlite">Browse historical
          source files</a></li>
     <li><a href="http://www.sqlite.org/docsrc/home">Website and documentation
          sources</a></li>
     </ul>
     </li>
<li> <a href="support.html">Support</a>







|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
     </ul></li>
<li> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> 
     <ul>
     <li> <a href="http://www.sqlite.org/cvstrac/timeline">Timeline</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/wiki">Wiki</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/tktnew">Report a bug</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/rptview?rn=5">Browse
            bug reports</a></li>
     <li><a href="http://www.sqlite.org/cvstrac/dir?d=sqlite">Browse historical
          source files</a></li>
     <li><a href="http://www.sqlite.org/docsrc/home">Website and documentation
          sources</a></li>
     </ul>
     </li>
<li> <a href="support.html">Support</a>