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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
d1d8642b57bd0765ade730248012d58b |
| User & Date: | drh 2002-05-15 11:43:16.000 |
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: 690f9a1631 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: d1d8642b57 user: drh tags: trunk) | |
| 08:43 | Add the Makefile.in that was forgotten with checkin #562 (CVS 563) (check-in: 29b8330ca6 user: danielk1977 tags: trunk) | |
Changes
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.35 2002/05/15 11:43:16 drh Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
341 342 343 344 345 346 347 348 349 350 351 352 353 354 |
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
</p>
}
Section {CREATE VIEW} {createview}
Syntax {sql-command} {
CREATE VIEW <view-name> AS <select-statement>
}
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 |
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
</p>
}
Section {CREATE TRIGGER} createtrigger
Syntax {sql-statement} {
CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event> ON <table-name>
<trigger-action>
}
Syntax {database-event} {
DELETE |
INSERT |
UPDATE |
UPDATE OF <column-list>
}
Syntax {trigger-action} {
[ FOR EACH ROW ] [ WHEN <expression> ]
BEGIN
<trigger-step> ; [ <trigger-step> ; ]*
END
}
Syntax {trigger-step} {
<update-statement> | <insert-statement> |
<delete-statement> | <select-statement>
}
puts {
<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>
<td valign="top" align="right" width=120><i>UPDATE</i></td>
<td valign="top">NEW and OLD references are valid</td>
</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>
<p>Triggers may be created on views, as well as ordinary tables. If one or
more INSERT, DELETE or 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>
}
Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
}
puts {
<p>With this trigger installed, executing the statement:</p>
}
Example {
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
}
puts {
<p>causes the following to be automatically executed:</p>
}
Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}
Section {CREATE VIEW} {createview}
Syntax {sql-command} {
CREATE VIEW <view-name> AS <select-statement>
}
|
| ︙ | ︙ | |||
393 394 395 396 397 398 399 400 401 402 403 404 405 406 |
}
Section {DROP TABLE} droptable
Syntax {sql-command} {
DROP TABLE <table-name>
}
puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table. The table named is completely removed from
the disk. The table can not be recovered. All indices associated with
the table are also deleted.</p>}
| > > > > > > > > > | 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 |
}
Section {DROP TABLE} droptable
Syntax {sql-command} {
DROP TABLE <table-name>
}
Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER <trigger-name>
}
puts {
<p>Used to drop a trigger from the database schema. Note that triggers
are automatically dropped when the associated table is dropped.</p>
}
puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table. The table named is completely removed from
the disk. The table can not be recovered. All indices associated with
the table are also deleted.</p>}
|
| ︙ | ︙ | |||
1087 1088 1089 1090 1091 1092 1093 | In version 1.0 of SQLite, the VACUUM command would invoke <b>gdbm_reorganize()</b> to clean up the backend database file. Beginning with version 2.0 of SQLite, GDBM is no longer used for the database backend and VACUUM has become a no-op. </p> } | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 |
In version 1.0 of SQLite, the VACUUM command would invoke
<b>gdbm_reorganize()</b> to clean up the backend database file.
Beginning with version 2.0 of SQLite, GDBM is no longer used for
the database backend and VACUUM has become a no-op.
</p>
}
puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>
</body></html>}
|