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.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bd0aa2fc3b188a2070ee218a1362cc708b26778f
User & Date: drh 2014-07-31 19:02:28.441
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
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234

1235
1236
1237
1238
1239
1240
1241
  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, 
respectively.  ^Instead,
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 {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;







|















|


|
>







1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
  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 triggers</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.  ^Instead,
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>Some Example Triggers</h3>

<p>^(Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following
UPDATE trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>

<tcl>Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
1249
1250
1251
1252
1253
1254
1255





























1256
1257
1258
1259
1260
1261
1262
}</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>)^






























<tcl>hd_fragment undef_before {undefined BEFORE trigger behavior}</tcl>
<h3>Cautions On The Use Of BEFORE triggers</h3>

<p>If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
that was to have been updated or deleted, then the result of the subsequent
update or delete operation is undefined.  Furthermore, if a BEFORE trigger







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







1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
}</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>For an example of an INSTEAD OF trigger, consider the following schema:

<tcl>Example {
CREATE TABLE customer(
  cust_id INTEGER PRIMARY KEY,
  cust_name TEXT,
  cust_addr TEXT
);
CREATE VIEW customer_address AS
   SELECT cust_id, cust_addr FROM customer;
CREATE TRIGGER cust_addr_chng
INSTEAD OF UPDATE OF cust_addr ON customer_address
BEGIN
  UPDATE customer SET cust_addr=NEW.cust_addr
   WHERE cust_id=NEW.cust_id;
END;
}</tcl>

<p>With the schema above, a statement of the form:</p>

<tcl>Example {
UPDATE customer_address SET cust_addr=$new_address WHERE cust_id=$cust_id;
}</tcl>

<p>Causes the customer.cust_addr field to be updated for a specific
customer entry that has customer.cust_id equal to the $cust_id parameter.
Note how the values assigned to the view are made available as field
in the special "NEW" table within the trigger body.</p>

<tcl>hd_fragment undef_before {undefined BEFORE trigger behavior}</tcl>
<h3>Cautions On The Use Of BEFORE triggers</h3>

<p>If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row
that was to have been updated or deleted, then the result of the subsequent
update or delete operation is undefined.  Furthermore, if a BEFORE trigger