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: |
bd0aa2fc3b188a2070ee218a1362cc70 |
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
Changes to pages/lang.in.
︙ | ︙ | |||
1208 1209 1210 1211 1212 1213 1214 | 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> | | | | > | 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 |
︙ | ︙ |