/ Check-in [d1d8642b]
Login

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

Overview
Comment:In the "lang.html" documentation file, put the CREATE TRIGGER and DROP TRIGGER sections in alphabetical order. (CVS 564)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d1d8642b57bd0765ade730248012d58b0859c12c
User & Date: drh 2002-05-15 11:43:16
Context
2002-05-15
11:44
Remove all tabs from the beginning of source code lines. Replace tabs with the appropriate number of spaces. (CVS 565) check-in: 690f9a16 user: drh tags: trunk
11:43
In the "lang.html" documentation file, put the CREATE TRIGGER and DROP TRIGGER sections in alphabetical order. (CVS 564) check-in: d1d8642b user: drh tags: trunk
08:43
Add the Makefile.in that was forgotten with checkin #562 (CVS 563) check-in: 29b8330c user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.34 2002/05/15 08:30:15 danielk1977 Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.35 2002/05/15 11:43:16 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   341    341   are read from the <b>sqlite_master</b> table and used to regenerate
   342    342   SQLite's internal representation of the table layout.
   343    343   If the original command was a CREATE TABLE AS then then an equivalent
   344    344   CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
   345    345   in place of the original command.
   346    346   </p>
   347    347   }
          348  +Section {CREATE TRIGGER} createtrigger
          349  +
          350  +Syntax {sql-statement} {
          351  +CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
          352  +<database-event> ON <table-name>
          353  +<trigger-action>
          354  +}
          355  +
          356  +Syntax {database-event} {
          357  +DELETE | 
          358  +INSERT | 
          359  +UPDATE | 
          360  +UPDATE OF <column-list>
          361  +}
          362  +
          363  +Syntax {trigger-action} {
          364  +[ FOR EACH ROW ] [ WHEN <expression> ] 
          365  +BEGIN 
          366  +  <trigger-step> ; [ <trigger-step> ; ]*
          367  +END
          368  +}
          369  +
          370  +Syntax {trigger-step} {
          371  +<update-statement> | <insert-statement> | 
          372  +<delete-statement> | <select-statement> 
          373  +}
          374  +
          375  +puts {
          376  +<p>The CREATE TRIGGER statement is used to add triggers to the 
          377  +database schema. Triggers are database operations (the <i>trigger-action</i>) 
          378  +that are automatically performed when a specified database event (the
          379  +<i>database-event</i>) occurs.  </p>
          380  +
          381  +<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a
          382  +particular database table occurs, or whenever an UPDATE of one or more
          383  +specified columns of a table are updated.</p>
          384  +
          385  +<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
          386  +STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
          387  +EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
          388  +may be executed (depending on the WHEN clause) for each database row being
          389  +inserted, updated or deleted by the statement causing the trigger to fire.</p>
          390  +
          391  +<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 
          392  +the row being inserted, deleted or updated using references of the form 
          393  +"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
          394  +<i>column-name</i> is the name of a column from the table that the trigger
          395  +is associated with. OLD and NEW references may only be used in triggers on
          396  +<i>trigger-event</i>s for which they are relevant, as follows:</p>
          397  +
          398  +<table border=0 cellpadding=10>
          399  +<tr>
          400  +<td valign="top" align="right" width=120><i>INSERT</i></td>
          401  +<td valign="top">NEW references are valid</td>
          402  +</tr>
          403  +<tr>
          404  +<td valign="top" align="right" width=120><i>UPDATE</i></td>
          405  +<td valign="top">NEW and OLD references are valid</td>
          406  +</tr>
          407  +<tr>
          408  +<td valign="top" align="right" width=120><i>DELETE</i></td>
          409  +<td valign="top">OLD references are valid</td>
          410  +</tr>
          411  +</table>
          412  +</p>
          413  +
          414  +<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>
          415  +
          416  +<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
          417  +will be executed relative to the insertion, modification or removal of the
          418  +associated row.</p>
          419  +
          420  +<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
          421  +<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
          422  +the statement causing the trigger to fire, then this conflict handling
          423  +policy is used instead.</p>
          424  +
          425  +<p>Triggers are automatically dropped when the table that they are 
          426  +associated with is dropped.</p>
          427  +
          428  +<p>Triggers may be created on views, as well as ordinary tables. If one or
          429  +more INSERT, DELETE or UPDATE triggers are defined on a view, then it is not
          430  +an error to execute an INSERT, DELETE or UPDATE statement on the view, 
          431  +respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view
          432  +causes the associated triggers to fire. The real tables underlying the view
          433  +are not modified (except possibly explicitly, by a trigger program).</p>
          434  +
          435  +<p><b>Example:</b></p>
          436  +
          437  +<p>Assuming that customer records are stored in the "customers" table, and
          438  +that order records are stored in the "orders" table, the following trigger
          439  +ensures that all associated orders are redirected when a customer changes
          440  +his or her address:</p>
          441  +}
          442  +Example {
          443  +CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
          444  +  BEGIN
          445  +    UPDATE orders SET address = new.address WHERE customer_name = old.name;
          446  +  END;
          447  +}
          448  +puts {
          449  +<p>With this trigger installed, executing the statement:</p>
          450  +}
          451  +Example {
          452  +UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
          453  +}
          454  +puts {
          455  +<p>causes the following to be automatically executed:</p>
          456  +}
          457  +Example {
          458  +UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
          459  +}
   348    460   
   349    461   Section {CREATE VIEW} {createview}
   350    462   
   351    463   Syntax {sql-command} {
   352    464   CREATE VIEW <view-name> AS <select-statement>
   353    465   }
   354    466   
................................................................................
   393    505   }
   394    506   
   395    507   Section {DROP TABLE} droptable
   396    508   
   397    509   Syntax {sql-command} {
   398    510   DROP TABLE <table-name>
   399    511   }
          512  +
          513  +Section {DROP TRIGGER} droptrigger
          514  +Syntax {sql-statement} {
          515  +DROP TRIGGER <trigger-name>
          516  +}
          517  +puts { 
          518  +  <p>Used to drop a trigger from the database schema. Note that triggers
          519  +  are automatically dropped when the associated table is dropped.</p>
          520  +}
   400    521   
   401    522   puts {
   402    523   <p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
   403    524   by the name of the table.  The table named is completely removed from
   404    525   the disk.  The table can not be recovered.  All indices associated with
   405    526   the table are also deleted.</p>}
   406    527   
................................................................................
  1087   1208   In version 1.0 of SQLite, the VACUUM command would invoke 
  1088   1209   <b>gdbm_reorganize()</b> to clean up the backend database file.
  1089   1210   Beginning with version 2.0 of SQLite, GDBM is no longer used for
  1090   1211   the database backend and VACUUM has become a no-op.
  1091   1212   </p>
  1092   1213   }
  1093   1214   
  1094         -Section {CREATE TRIGGER} createtrigger
  1095         -
  1096         -Syntax {sql-statement} {
  1097         -CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
  1098         -<database-event>
  1099         -<trigger-action>
  1100         -}
  1101         -
  1102         -Syntax {database-event} {
  1103         -DELETE | 
  1104         -INSERT | 
  1105         -UPDATE | 
  1106         -UPDATE OF <column-list>
  1107         -ON <table-name> 
  1108         -}
  1109         -
  1110         -Syntax {trigger-action} {
  1111         -[ FOR EACH ROW ] [ WHEN <expression> ] 
  1112         -BEGIN 
  1113         -  <trigger-step> ; [ <trigger-step> ; ]*
  1114         -END
  1115         -}
  1116         -
  1117         -Syntax {trigger-step} {
  1118         -<update-statement> | <insert-statement> | 
  1119         -<delete-statement> | <select-statement> 
  1120         -}
  1121         -
  1122         -puts {
  1123         -<p>The CREATE TRIGGER statement is used to add triggers to the 
  1124         -database schema. Triggers are database operations (the <i>trigger-action</i>) 
  1125         -that are automatically performed when a specified database event (the
  1126         -<i>database-event</i>) occurs.  </p>
  1127         -
  1128         -<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a
  1129         -particular database table occurs, or whenever an UPDATE of one or more
  1130         -specified columns of a table are updated.</p>
  1131         -
  1132         -<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
  1133         -STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
  1134         -EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
  1135         -may be executed (depending on the WHEN clause) for each database row being
  1136         -inserted, updated or deleted by the statement causing the trigger to fire.</p>
  1137         -
  1138         -<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 
  1139         -the row being inserted, deleted or updated using references of the form 
  1140         -"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
  1141         -<i>column-name</i> is the name of a column from the table that the trigger
  1142         -is associated with. OLD and NEW references may only be used in triggers on
  1143         -<i>trigger-event</i>s for which they are relevant, as follows:</p>
  1144         -
  1145         -<table border=0 cellpadding=10>
  1146         -<tr>
  1147         -<td valign="top" align="right" width=120><i>INSERT</i></td>
  1148         -<td valign="top">NEW references are valid</td>
  1149         -</tr>
  1150         -<tr>
  1151         -<td valign="top" align="right" width=120><i>UPDATE</i></td>
  1152         -<td valign="top">NEW and OLD references are valid</td>
  1153         -</tr>
  1154         -<tr>
  1155         -<td valign="top" align="right" width=120><i>DELETE</i></td>
  1156         -<td valign="top">OLD references are valid</td>
  1157         -</tr>
  1158         -</table>
  1159         -</p>
  1160         -
  1161         -<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>
  1162         -
  1163         -<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
  1164         -will be executed relative to the insertion, modification or removal of the
  1165         -associated row.</p>
  1166         -
  1167         -<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
  1168         -<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
  1169         -the statement causing the trigger to fire, then this conflict handling
  1170         -policy is used instead.</p>
  1171         -
  1172         -<p>Triggers are automatically dropped when the table that they are 
  1173         -associated with is dropped.</p>
  1174         -
  1175         -<p>Triggers may be created on views, as well as ordinary tables. If one or
  1176         -more INSERT, DELETE or UPDATE triggers are defined on a view, then it is not
  1177         -an error to execute an INSERT, DELETE or UPDATE statement on the view, 
  1178         -respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view
  1179         -causes the associated triggers to fire. The real tables underlying the view
  1180         -are not modified (except possibly explicitly, by a trigger program).</p>
  1181         -
  1182         -<p><b>Example:</b></p>
  1183         -
  1184         -<p>Assuming that customer records are stored in the "customers" table, and
  1185         -that order records are stored in the "orders" table, the following trigger
  1186         -ensures that all associated orders are redirected when a customer changes
  1187         -his or her address:</p>
  1188         -}
  1189         -Example {
  1190         -CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  1191         -  BEGIN
  1192         -    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  1193         -  END;
  1194         -}
  1195         -puts {
  1196         -<p>With this trigger installed, executing the statement:</p>
  1197         -}
  1198         -Example {
  1199         -UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
  1200         -}
  1201         -puts {
  1202         -<p>causes the following to be automatically executed:</p>
  1203         -}
  1204         -Example {
  1205         -UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
  1206         -}
  1207         -
  1208         -Section {DROP TRIGGER} droptrigger
  1209         -Syntax {sql-statement} {
  1210         -DROP TRIGGER <trigger-name>
  1211         -}
  1212         -puts { 
  1213         -  <p>Used to drop a trigger from the database schema. Note that triggers
  1214         -  are automatically dropped when the associated table is dropped.</p>
  1215         -}
  1216         -
  1217   1215   
  1218   1216   puts {
  1219   1217   <p><hr /></p>
  1220   1218   <p><a href="index.html"><img src="/goback.jpg" border=0 />
  1221   1219   Back to the SQLite Home Page</a>
  1222   1220   </p>
  1223   1221   
  1224   1222   </body></html>}