SQLite的SQL语法详解(3)

CREATE TRIGGER sql-statement ::=   CREATE [TEMP | TEMPORARY] TRIGGER trigger-name[ BEFORE | AFTER ]
database-event ON [database-name .]table-name
trigger-action  
sql-statement ::=   CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .]view-name
trigger-action  
database-event ::=   DELETE |
INSERT |
UPDATE |
UPDATE OF column-list  
trigger-action ::=   [ FOR EACH ROW | FOR EACH STATEMENT ][ WHEN expression]
BEGIN
trigger-step ; [trigger-step ; ]*
END  
trigger-step ::=   update-statement|insert-statement|
delete-statement|select-statement  

CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件（database-event）发生时自动进行的数据库操作（trigger-action）。

触发器可由在特殊表上执行的DELETE、INSERT、UPDATE等语句触发，或UPDATE表中特定的字段时触发。

现在SQLite仅支持FOR EACH ROW触发器，不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW。FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在（由WHEN子句决定的）数据库插入，更改或删除的每一行触发trigger。

WHEN子句和trigger-steps可以使用“NEW.column-name”和“OLD.column-name”的引用形式访问正在被插入，更改或删除的行的元素，column-name是触发器关联的表中的字段名。OLD和NEW引用只在触发器与之相关的trigger-event处可用，例如：

INSERT   NEW可用  
UPDATE   NEW和OLD均可用  
DELETE   OLD可用  

当使用WHEN子句，trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。

trigger-time决定了trigger-steps执行的时间，它是相对于关联行的插入、删除和修改而言的。

作为的一部分trigger-step的UPDATE或INSERT可以使用ON CONFLICT子句。但若触发trigger的语句使用了ON CONFLICT子句，则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。

关联表被撤销时触发器被自动删除。

不仅在表上，在视图上一样可以创建触发器，在CREATE TRIGGER语句中使用INSTEAD OF即可。若视图上定义了一个或多个ON INSERT、ON DELETE、ON UPDATE触发器，则相应地对视图执行INSERT、DELETE或UPDATE语句不会出错，而会触发关联的触发器。视图关联的表不会被修改。（除了由触发器进行的修改操作）。

例子：

假设“customers”表存储了客户信息，“orders”表存储了订单信息，下面的触发器确保当用户改变地址时所有的关联订单地址均进行相应改变：

CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END;

定义了该触发器后执行如下语句：

UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’;

会使下面的语句自动执行：

UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;

注意，目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的INTEGER PRIMARY KEY域，而该域将由触发该触发器的语句进行修改，则可能根本不会修改该域。可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。

一个特殊的SQL函数RAISE()可用于触发器程序，使用如下语法：

raise-function ::=   RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )  

当触发器程序执行中调用了上述前三个之一的形式时，则执行指定的ON CONFLICT进程（ABORT、FAIL或者ROLLBACK）且终止当前查询，返回一个SQLITE_CONSTRAINT错误并说明错误信息。

当调用RAISE(IGNORE)，当前触发器程序的余下部分，触发该触发器的语句和任何之后的触发器程序被忽略并且不恢复对数据库的已有改变。若触发触发器的语句是一个触发器程序本身的一部分，则原触发器程序从下一步起继续执行。

使用DROP TRIGGER删除触发器。

CREATE VIEW sql-command ::=   CREATE [TEMP | TEMPORARY] VIEW [database-name.]view-name AS select-statement  

CREATE VIEW命令为一个包装好的SELECT语句命名。当创建了一个视图，它可以用于其他SELECT的FROM字句中代替表名。

若“TEMP”或“TEMPORARY”关键字出现在“CREATE”和“VIEW”之间，则创建的视图仅对打开数据库的进程可见，且在数据库关闭时自动删除。

若指定了则视图在指定的数据库中创建。同时使用和TEMP关键字会导致错误，除非是“temp”。若不声明数据库名，也不使用TEMP关键字，则视图创建于主数据库中。

你不能对视图使用COPY、DELETE、INSERT或UPDATE，视图在SQLite中是只读的。多数情况下你可以在视图上创建TRIGGER来达到相同目的。用DROP VIEW命令来删除视图。

DELETE sql-statement ::=   DELETE FROM [database-name .]table-name[WHERE expr]  

DELETE命令用于从表中删除记录。命令包含“DELETE FROM”关键字以及需要删除的记录所在的表名。

若不使用WHERE子句，表中的所有行将全部被删除。否则仅删除符合条件的行。

DETACH DATABASE sql-command ::=   DETACH [DATABASE]database-name  

该语句拆分一个之前使用ATTACH DATABASE语句附加的数据库连接。可以使用不同的名字多次附加同一数据库，并且拆分一个连接不会影响其他连接。

若SQLite在事务进行中，该语句不起作用。

DROP INDEX sql-command ::=   DROP INDEX [IF EXISTS][database-name .]index-name  

DROP INDEX语句删除由CREATE INDEX语句创建的索引。索引将从数据库结构和磁盘文件中完全删除，唯一的恢复方法是重新输入相应的CREATE INDEX命令。

DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用VACUUM命令。若AUTOVACUUM模式开启，则空间会自动被DROP INDEX释放。

DROP TABLE sql-command ::=   DROP TABLE [IF EXISTS][database-name.]table-name  

DROP TABLE语句删除由CREATE TABLE语句创建的表。表将从数据库结构和磁盘文件中完全删除，且不能恢复。该表的所有索引也同时被删除。

DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间，可以使用VACUUM命令。若AUTOVACUUM模式开启，则空间会自动被DROP TABLE释放。

若使用可选的IF EXISTS子句，在删除的表不存在时就不会报错。