Choose any three.
|
|
SQLite里的SQL
SQL As Understood By SQLite
CREATE TRIGGER
CREATE TRIGGER语句用于在数据库结构中添加一个触发器。触发器可以使数据库在指定的数据库事件发生时自动执行一些数据库操作。
The CREATE TRIGGER statement is used to add triggers to the
database schema. Triggers are database operations
that are automatically performed when a specified database event
occurs.
触发器可以在某个数据库表上发生DELETE、 INSERT或
UPDATE事件时触发，也可以在一个表上的一个或多个列上发生UPDATE事件时触发。
A trigger may be specified to fire whenever a DELETE, INSERT,
or UPDATE of a
particular database table occurs, or whenever an UPDATE occurs on
on one or more specified columns of a table.
目前SQLite只支持FOR EACH ROW 触发器，不吃食FOR EACH STATEMENT触发器。
因此，不需要总是明确指定FOR EACH ROW。FOR EACH ROW是指，当有语句触发触发器时，数据库中每一行记录的插入、更新和删除都会执行（依赖于WHEN子句）触发器中指定的SQL语句。
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 in the trigger
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.
WHEN子句和触发器动作都可以可以通过"NEW.column-name" 和 "OLD.column-name"来引用被插入、删除或修改的数据行中的元素，其中column-name是触发器所关联的表的列名。
在不同的触发器事件中OLD和NEW引用是否有效依赖于下表：
Both the WHEN clause and the trigger actions may access elements of
the row being inserted, deleted or updated using references of the form
"NEW.column-name" and "OLD.column-name", where
column-name 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
events for which they are relevant, as follows:
| INSERT | NEW引用有效
NEW references are valid |
| UPDATE | NEW和OLD引用有效
NEW and OLD references are valid |
| DELETE | OLD引用有效
OLD references are valid |
如果提供了WHEN子句，那么只有WHEN子句为true记录行才会执行指定的语句。如果没有提供WHEN子句，那么所有的行都会执行SQL语句。
If a WHEN clause is supplied, the SQL statements specified
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.
BEFORE和AFTER关键词用于决定相关行上触发器动作与插入、修改、移除执行顺序的关系。
The BEFORE or AFTER keyword determines when the trigger actions
will be executed relative to the insertion, modification or removal of the
associated row.
在触发器中的UPDATE 和 INSERT可以指定一个ON CONFLICT子句。但是，如果触发触发器的语句也指定了一个ON CONFLICT子句，那么会用外层的冲突处理策略替换。
An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
action within the body of the trigger.
However if an ON CONFLICT clause is specified as part of
the statement causing the trigger to fire, then conflict handling
policy of the outer statement is used instead.
当触发器使用table-name依附的表被删除了，那么依附在这个表上的触发器也都会被自动删除。但是，如果触发器行为中引用的其它表被删除 或 修改了，触发器是不会被删除的。
Triggers are automatically dropped
when the table that they are
associated with (the table-name table) is
dropped. However if the trigger actions reference
other tables, the trigger is not dropped or modified if those other
tables are dropped or modified.
触发器使用DROP TRIGGER语句来移除。
Triggers are removed using the DROP TRIGGER statement.
触发器中使用UPDATE、 DELETE和 INSERT语句的语法限制
Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
Triggers
触发器内部的UPDATE、 DELETE 和 INSERT语句并不支持UPDATE、 DELETE 和 INSERT的完整语法，需要遵守下列限制：
The UPDATE, DELETE, and INSERT
statements within triggers do not support
the full syntax for UPDATE, DELETE, and INSERT statements. The following
restrictions apply:
UPDATE、 DELETE 和 INSERT语句中需要修改的表明必须是无限定的表明。换句话说，在指定表的时候必须使用"tablename"而不能使用"database.tablename"。被修改的表必须和触发器依附的表或视图在一个数据库中。
The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table. The table to be modified must exist in the same database as the table or view to which the trigger is attached.不支持INSERT语句中的"INSERT INTO table DEFAULT VALUES"格式。
The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.不支持UPDATE 和 DELETE语句中的INDEXED BY和NOT INDEXED子句。
The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.不支持UPDATE 和 DELETE语句中的ORDER BY和LIMIT子句。UPDATE 和 DELETE中的ORDER BY和LIMIT通常在任何上下文中都是不能使用的，不过在使用了SQLITE_ENABLE_UPDATE_DELETE_LIMIT编译选项后，可以再顶级的语句中使用。但是编译选项只允许顶级UPDATE 和 DELETE使用，而不允许触发器内的UPDATE 和 DELETE使用。
The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements 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.
INSTEAD OF触发器
INSTEAD OF trigger
通过在CREATE TRIGGER语句中指定INSTEAD OF可以在视图上创建一个触发器，就像普通表格一样。
如果在一个视图上定义多个ON INSERT、ON DELETE或者ON UPDATE触发器，那么在视图上分别执行INSERT、 DELETE或UPDATE语句是不会导致错误的。事实上，在视图上执行一个INSERT、DELETE或者UPDATE会触发相关联的触发器，但是视图下的实际表格是不会被修改的（除非在触发器程序中特别指定）。
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).
注意，sqlite3_changes()和sqlite3_total_changes()接口是不会统计INSERT OF触发器事件的，不过count_changes 指令会统计INSTEAD OF触发器事件。
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.
举例
Examples
假设，顾客记录存储在"customers"表中，订单记录存储在"orders"表中，下面的触发器可以确保当顾客修改了他们的地址时所有关联的订单可以更新。
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:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
当加入了这个触发器后，执行语句：
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
会触发下面的语句自动执行：
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
使用BEFORE触发器的注意事项
Cautions On The Use Of BEFORE triggers
如果一个BEFORE UPDATE 或 BEFORE DELETE触发器修改或删除那个即将要被更新或删除的记录行，那么后面的更新或删除操作的结果是未知的。而且，如果一个BEFORE触发器修改或删除一行，那么这些行上的其他一些AFTER触发器是否会真正执行是未知的。
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
modifies or deletes a row, then it is undefined whether or not AFTER triggers
that would have otherwise run on those rows will in fact run.
如果rowid没有明确指定为一个整数，那么在BEFORE INSERT触发器中NEW.rowid的值是未知的
The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which
the rowid is not explicitly set to an integer.
正是由于上面的情况，程序员们相比BEFORE触发器而言更喜欢AFTER触发器。
Because of the behaviors described above, programmers are encouraged to
prefer AFTER triggers over BEFORE triggers.
RAISE()函数
The RAISE() function
在触发器程序中可以按照下面的语法使用一个特殊的SQL函数RAISE()。
A special SQL function RAISE() may be used within a trigger-program,
with the following syntax
如果在触发器程序执行中调用了RAISE(ROLLBACK,...)、 RAISE(ABORT,...) 或 RAISE(FAIL,...)之一，那么指定的ON CONFLICT处理逻辑会执行而当前查询会终止，并且会返回给应用一个SQLITE_CONSTRAINT错误码和一个指定的错误信息。
When one of RAISE(ROLLBACK,...), RAISE(ABORT,...) or RAISE(FAIL,...)
is called during trigger-program
execution, the specified ON CONFLICT processing is performed
the current query terminates.
An error code of SQLITE_CONSTRAINT is returned to the application,
along with the specified error message.
如果调用了RAISE(IGNORE)，那么触发器程序中剩余的部分、触发触发器的语句和其他后续的应当执行的触发器都会被抛弃。数据库修改不会回滚。如果触发触发器的语句就在这个触发器程序中，那么这个触发器程序会在下一步开始处恢复执行。
When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would have been executed are abandoned. No database
changes are rolled back. If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
非临时表上的临时触发器
TEMP Triggers on Non-TEMP Tables
触发器通常与CREATE TRIGGER语句中"ON"关键词后面指定的表名在同一个数据库中。不过，可以在另一个数据库上为该表创建一个临时触发器。这种触发器只有当定义这个触发器的程序在目标表格上产生了修改才会触发。其他修改数据库的应用程序则看不到这个临时触发器，也就无法运行这个触发器。
A trigger normally exists in the same database as the table named
after the "ON" keyword in the CREATE TRIGGER statement. Except, it is
possible to create a TEMP TRIGGER on a table in another database.
Such a trigger will only fire when changes
are made to the target table by the application that defined the trigger.
Other applications that modify the database will not be able to see the
TEMP trigger and hence cannot run the trigger.
当为一个非临时表定义临时触发器时，指定存储这个非临时表的数据库是非常重要的。例如，在下面的语句中，用"main.tab1"替换"tabl1"是非常重要的。
When defining a TEMP trigger on a non-TEMP table, it is important to
specify the database holding the non-TEMP table. For example,
in the following statement, it is important to say "main.tab1" instead
of just "tab1":
CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN ...
如果未能给目标表格指定数据库名，那么当数据库结构发生变化时，临时触发器可能会重新附加到其他数据库上同名的表上。
Failure to specify the database name on the target table could result
in the TEMP trigger being reattached to a table with the same name in
another database whenever any schema change occurs.