Documentation Source Text

Check-in [bd0aa2fc3b]
Login

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

Overview
Comment:Clarification to the "Examples" section on the CREATE TRIGGER documentation page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bd0aa2fc3b188a2070ee218a1362cc708b26778f
User & Date: drh 2014-07-31 19:02:28
Context
2014-07-31
19:04
Add the DISTINCT optimization to the change log for 3.8.6. check-in: 06696e7778 user: drh tags: trunk
19:02
Clarification to the "Examples" section on the CREATE TRIGGER documentation page. check-in: bd0aa2fc3b user: drh tags: trunk
2014-07-30
14:31
Update the change log to include the CREATE UNIQUE INDEX fix. check-in: dfe6ec1900 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

  1208   1208     using the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option.  However,
  1209   1209     that compile-time option only applies to top-level [UPDATE] and [DELETE]
  1210   1210     statements, not [UPDATE] and [DELETE] statements within triggers.)^
  1211   1211     </p></li>
  1212   1212   </ul>
  1213   1213   
  1214   1214   <tcl>hd_fragment instead_of_trigger {INSTEAD OF} {INSTEAD OF trigger}</tcl>
  1215         -<h3>INSTEAD OF trigger</h3>
         1215  +<h3>INSTEAD OF triggers</h3>
  1216   1216   
  1217   1217   <p>^Triggers may be created on [views], as well as ordinary tables, by
  1218   1218   specifying INSTEAD OF in the CREATE TRIGGER statement. 
  1219   1219   ^If one or more ON INSERT, ON DELETE
  1220   1220   or ON UPDATE triggers are defined on a view, then it is not an
  1221   1221   error to execute an INSERT, DELETE or UPDATE statement on the view, 
  1222   1222   respectively.  ^Instead,
................................................................................
  1224   1224   triggers to fire. ^The real tables underlying the view are not modified
  1225   1225   (except possibly explicitly, by a trigger program).</p>
  1226   1226   
  1227   1227   <p>^Note that the [sqlite3_changes()] and [sqlite3_total_changes()] interfaces
  1228   1228   do not count INSTEAD OF trigger firings, but the
  1229   1229   [count_changes pragma] does count INSTEAD OF trigger firing.</p>
  1230   1230   
  1231         -<h3>Examples</h3>
         1231  +<h3>Some Example Triggers</h3>
  1232   1232   
  1233   1233   <p>^(Assuming that customer records are stored in the "customers" table, and
  1234         -that order records are stored in the "orders" table, the following trigger
         1234  +that order records are stored in the "orders" table, the following
         1235  +UPDATE trigger
  1235   1236   ensures that all associated orders are redirected when a customer changes
  1236   1237   his or her address:</p>
  1237   1238   
  1238   1239   <tcl>Example {
  1239   1240   CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  1240   1241     BEGIN
  1241   1242       UPDATE orders SET address = new.address WHERE customer_name = old.name;
................................................................................
  1249   1250   }</tcl>
  1250   1251   
  1251   1252   <p>causes the following to be automatically executed:</p>
  1252   1253   
  1253   1254   <tcl>Example {
  1254   1255   UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
  1255   1256   }</tcl>)^
         1257  +
         1258  +<p>For an example of an INSTEAD OF trigger, consider the following schema:
         1259  +
         1260  +<tcl>Example {
         1261  +CREATE TABLE customer(
         1262  +  cust_id INTEGER PRIMARY KEY,
         1263  +  cust_name TEXT,
         1264  +  cust_addr TEXT
         1265  +);
         1266  +CREATE VIEW customer_address AS
         1267  +   SELECT cust_id, cust_addr FROM customer;
         1268  +CREATE TRIGGER cust_addr_chng
         1269  +INSTEAD OF UPDATE OF cust_addr ON customer_address
         1270  +BEGIN
         1271  +  UPDATE customer SET cust_addr=NEW.cust_addr
         1272  +   WHERE cust_id=NEW.cust_id;
         1273  +END;
         1274  +}</tcl>
         1275  +
         1276  +<p>With the schema above, a statement of the form:</p>
         1277  +
         1278  +<tcl>Example {
         1279  +UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_id;
         1280  +}</tcl>
         1281  +
         1282  +<p>Causes the customer.cust_addr field to be updated for a specific
         1283  +customer entry that has customer.cust_id equal to the $cust_id parameter.
         1284  +Note how the values assigned to the view are made available as field
         1285  +in the special "NEW" table within the trigger body.</p>
  1256   1286   
  1257   1287   <tcl>hd_fragment undef_before {undefined BEFORE trigger behavior}</tcl>
  1258   1288   <h3>Cautions On The Use Of BEFORE triggers</h3>
  1259   1289   
  1260   1290   <p>If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
  1261   1291   that was to have been updated or deleted, then the result of the subsequent
  1262   1292   update or delete operation is undefined.  Furthermore, if a BEFORE trigger