sqlite3触发器的使用

研究了一下osx下dock中应用的存储，位于~/Library/Application Support/Dock/下一个比較名字比較长的db文件里，之前简单的介绍过osx launchpad图标的删除,这里对db文件进行了分析。

osx中db文件是sqlite3数据库相应的数据库文件，学过andorid或者ios开发的朋友应该比較的熟悉，查看数据库能够看到该数据库(名字比較长的db文件)中存在一下表:
app_sources       dbinfo            image_cache       widgets         apps              downloading_apps  items           categories        groups            widget_sources 

当中比較重要的2个表要数apps与items表了，可是那个算是最主要的表呢，查看了一下表结构例如以下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

sqlite > .schema apps CREATE TABLE apps  (item_id INTEGER PRIMARY KEY, title VARCHAR, bundleid VARCHAR, storeid VARCHAR,category_id INTEGER, moddate REAL, bookmark BLOB ); sqlite > .schema items CREATE TABLE items  (rowid INTEGER PRIMARY KEY ASC, uuid VARCHAR, flags INTEGER,  type INTEGER, parent_id INTEGER NOT NULL, ordering INTEGER ); CREATE TRIGGER update_items_order BEFORE UPDATE OF ordering ON items WHEN new.ordering  > old.ordering AND  0 ==  (SELECT value FROM dbinfo WHERE  key= 'ignore_items_update_triggers' ) BEGIN UPDATE dbinfo SET  value= 1 WHERE  key= 'ignore_items_update_triggers'; UPDATE items SET ordering = ordering -  1 WHERE parent_id = old.parent_id AND ordering BETWEEN old.ordering and new.ordering; UPDATE dbinfo SET  value= 0 WHERE  key= 'ignore_items_update_triggers'; END; CREATE TRIGGER update_items_order_backwards BEFORE UPDATE OF ordering ON items WHEN new.ordering  < old.ordering AND  0 ==  (SELECT value FROM dbinfo WHERE  key= 'ignore_items_update_triggers' ) BEGIN UPDATE dbinfo SET  value= 1 WHERE  key= 'ignore_items_update_triggers'; UPDATE items SET ordering = ordering +  1 WHERE parent_id = old.parent_id AND ordering BETWEEN new.ordering and old.ordering; UPDATE dbinfo SET  value= 0 WHERE  key= 'ignore_items_update_triggers'; END; CREATE TRIGGER update_item_parent AFTER UPDATE OF parent_id ON items BEGIN UPDATE dbinfo SET  value= 1 WHERE  key= 'ignore_items_update_triggers'; UPDATE items SET ordering =  (SELECT ifnull (MAX (ordering ), 0 )+ 1 FROM items WHERE  parent_id=new.parent_id AND ROWID !=old.rowid ) WHERE  ROWID=old.rowid; UPDATE items SET ordering = ordering -  1 WHERE parent_id = old.parent_id and ordering  > old.ordering; UPDATE dbinfo SET  value= 0 WHERE  key= 'ignore_items_update_triggers'; END; CREATE TRIGGER insert_item AFTER INSERT on items WHEN  0 ==  (SELECT value FROM dbinfo WHERE  key= 'ignore_items_update_triggers' ) BEGIN UPDATE dbinfo SET  value= 1 WHERE  key= 'ignore_items_update_triggers'; UPDATE items SET ordering =  (SELECT ifnull (MAX (ordering ), 0 )+ 1 FROM items WHERE  parent_id=new.parent_id ) WHERE  ROWID=new.rowid; UPDATE dbinfo SET  value= 0 WHERE  key= 'ignore_items_update_triggers'; END; CREATE TRIGGER app_inserted AFTER INSERT ON items WHEN new.type =  4 OR new.type =  5 BEGIN INSERT INTO image_cache VALUES  (new.rowid, 0, 0,NULL,NULL ); END; CREATE TRIGGER widget_inserted AFTER INSERT ON items WHEN new.type =  6 OR new.type =  7 BEGIN INSERT INTO image_cache VALUES  (new.rowid, 0, 0,NULL,NULL ); END; CREATE TRIGGER app_deleted AFTER DELETE ON items WHEN old.type =  4 OR old.type =  5 BEGIN DELETE FROM image_cache WHERE  item_id=old.rowid; END; CREATE TRIGGER widget_deleted AFTER DELETE ON items WHEN old.type =  6 OR old.type =  7 BEGIN DELETE FROM image_cache WHERE  item_id=old.rowid; END; CREATE TRIGGER item_deleted AFTER DELETE ON items BEGIN DELETE FROM apps WHERE  rowid=old.rowid; DELETE FROM  groups WHERE  item_id=old.rowid; DELETE FROM widgets WHERE  rowid=old.rowid; DELETE FROM downloading_apps WHERE  item_id=old.rowid; UPDATE dbinfo SET  value= 1 WHERE  key= 'ignore_items_update_triggers'; UPDATE items SET ordering = ordering -  1 WHERE old.parent_id = parent_id AND ordering  > old.ordering; UPDATE dbinfo SET  value= 0 WHERE  key= 'ignore_items_update_triggers'; END; CREATE INDEX items_uuid_index ON items  (uuid ); CREATE INDEX items_ordering_index ON items  (parent_id,ordering ); CREATE INDEX items_type ON items  ( type ); sqlite >

从上面能够看出items相应了非常多的触发器，所以items应该是基表。说到了触发器，以下開始说我们的正题。

当数据库中表中的数据发生变化时，包含insert,update,delete随意操作，假设我们对该表写了相应的DML触发器，那么该触发器自己主动运行。DML触发器的主要作用在于强制运行业 务规则，以及扩展Sql Server约束，默认值等。由于我们知道约束仅仅能约束同一个表中的数据，而触发器中则能够运行随意Sql命令。

以下从一个样例来介绍一下触发器的使用。数据库中有3个表，src、backup、del，分表代表原数据表，备份数据表和删除数据表，我们如今要实现的是对原数据的插入与更新以及删除要同步到backup表中，对原数据的删除，要将删除的信息写入del表中，也就是要保证src表与backup表是时刻一模一样的，del表存储删除的信息。

触发器的创建例如以下:

1 2 3 4 5 6

CREATE TRIGGER  <  [ BEFORE  | AFTER  ]  >  <  [ INSERT  | UPDATE  | DELETE  ]  > ON  <tableName >  //dbo代表该表的全部者 FOR EACH ROW BEGIN --do something END ;

三个表都很的简单，仅仅有一个int类型的id属性。创建表以及触发器例如以下:

1 2 3 4 5 6

CREATE TABLE src ( id int ); CREATE TABLE backup ( id int ); CREATE TABLE del ( id int ); CREATE TRIGGER trigger1 after insert on src begin insert into backup values (new.id ); end; CREATE TRIGGER trigger2 after update on src begin update backup  set  id=new.id where  id=old.id ; end; CREATE TRIGGER trigger3 after delete on src begin insert into del values (old.id ) ; delete from backup where  id=old.id; end;

上面的3个触发器的意思还是非常easy理解的，我们须要注意的一点是在begin与end之间的操作中，对前面作用表的新旧数据使用new和old进行指向，如在src表进行插入操作后，会处罚trigger1，此时trigger将src中id的新值(new.id)插入带backup表中。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

sqlite > insert into src values ( 1 ) ;   //插入数据 1 sqlite > insert into src values ( 2 ) ;   //插入数据 2 sqlite >  select  * from src ; 1 2 sqlite >  select  * from backup ; 1 2 sqlite >  select  * from del ; sqlite > update src  set  id= 3 where  id= 2 ;   //更新数据 sqlite >  select  * from src ; 1 3 sqlite >  select  * from backup ; 1 3 sqlite >  select  * from del ; sqlite > delete from src where  id= 1;   //删除数据 sqlite >  select  * from src ; 3 sqlite >  select  * from backup ; 3 sqlite >  select  * from del ; 1 sqlite >

能够看出，在src表发生更删改的时候，触发器启动了，运行了对应的操作，保证了数据的统一性。

很多其它文章请前往小胖轩.