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表了，但是那个算是最基本的表呢，查看了一下表结构如下:

 
  
   
     
    
       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,typeINTEGER,parent_id INTEGER NOT NULL,ordering INTEGER 
      ); 
      
 CREATE TRIGGER update_items_order BEFORE UPDATE OF ordering ON items WHEN new.ordering 
      &gt; old.ordering AND 
      0== 
      (SELECT value FROM dbinfo WHERE 
      key= 
      'ignore_items_update_triggers' 
      ) 
      
 BEGIN 
      
 UPDATE dbinfo SET 
      value= 
      1WHERE 
      'ignore_items_update_triggers'; 
      
 UPDATE items SET ordering = ordering - 
      1WHERE parent_id = old.parent_id AND ordering BETWEEN old.ordering and new.ordering; 
      
 UPDATE dbinfo SET 
      0WHERE 
      'ignore_items_update_triggers'; 
      
 END; 
      
 CREATE TRIGGER update_items_order_backwards BEFORE UPDATE OF ordering ON items WHEN new.ordering 
      &lt; old.ordering AND 
      'ignore_items_update_triggers' 
      )BEGIN UPDATE dbinfo SET 
      'ignore_items_update_triggers'; UPDATE items SET ordering = ordering + 
      1WHERE parent_id = old.parent_id AND ordering BETWEEN new.ordering and old.ordering; UPDATE dbinfo SET 
      'ignore_items_update_triggers'; END; CREATE TRIGGER update_item_parent AFTER UPDATE OF parent_id ON items BEGIN UPDATE dbinfo SET 
      'ignore_items_update_triggers'; UPDATE items SET ordering = 
      (SELECT ifnull 
      (MAX 
      (ordering 
      ),0 
      )+ 
      1FROM items WHERE 
      parent_id=new.parent_id AND ROWID 
      !=old.rowid 
      )WHERE 
      ROWID=old.rowid; UPDATE items SET ordering = ordering - 
      1WHERE parent_id = old.parent_id and ordering 
      &gt; old.ordering; 
      
 UPDATE dbinfo SET 
      'ignore_items_update_triggers'; 
      
 END; 
      
 CREATE TRIGGER insert_item AFTER INSERT on items WHEN 
      'ignore_items_update_triggers'; 
      
 UPDATE items SET ordering = 
      (SELECT ifnull 
      (MAX 
      (ordering 
      ),128)">parent_id=new.parent_id 
      )WHERE 
      ROWID=new.rowid; 
      
 UPDATE dbinfo SET 
      'ignore_items_update_triggers'; 
      
 END; 
      
 CREATE TRIGGER app_inserted AFTER INSERT ON items WHEN new.type = 
      4OR new.type = 
      5 
      
 BEGIN 
      
 INSERT INTO image_cache VALUES 
      (new.rowid,160)">0,NULL,NULL 
      ); 
      
 END; 
      
 CREATE TRIGGER widget_inserted AFTER INSERT ON items WHEN new.type = 
      6OR new.type = 
      7 
      
 BEGIN 
      
 INSERT INTO image_cache VALUES 
      (new.rowid,NULL 
      ); 
      
 END; 
      
 CREATE TRIGGER app_deleted AFTER DELETE ON items WHEN old.type = 
      4OR 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 = 
      6OR 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 
      groupsWHERE 
      item_id=old.rowid; 
      
 DELETE FROM widgets WHERE 
      rowid=old.rowid; 
      
 DELETE FROM downloading_apps WHERE 
      item_id=old.rowid; 
      
 UPDATE dbinfo SET 
      1WHERE old.parent_id = parent_id AND ordering 
      &gt; old.ordering; 
      
 UPDATE dbinfo SET 
      '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 
      
 
      --dosomething 
      
 END ; 
     

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

CREATE TABLE src ( idint );
CREATE TABLE backup ( idint );
CREATE TABLE del ( idint );
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个触发器的意思还是很容易理解的，我们需要注意的一点是在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
select *from backup ;
select *from del ;
sqlite >update src id= 3where 2; //更新数据
sqlite > 3
sqlite > select *from del ;
sqlite >delete from src where 1; //删除数据
sqlite > select *from del ;
1
sqlite >

可以看出，在src表发生更删改的时候，触发器启动了，执行了相应的操作，保证了数据的统一性。

更多文章请前往小胖轩.

sqlite3触发器的使用

猜你在找的Sqlite相关文章