sqlite3触发器的使用-白红宇

sqlite3触发器的使用

阅读量：6230 次

发布时间：2019-06-21

本文共 5655 字，大约阅读时间需要 18 分钟。

研究了一下osx下dock中应用的存储，位于~/Library/Application Support/Dock/下一个比較名字比較长的db文件里，之前简单的介绍过,这里对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,  
type INTEGER, 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= 
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  
&lt; 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  
&gt; 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  
&gt; 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表存储删除的信息。

触发器的创建例如以下:

                         
           CREATE TRIGGER  
<
[ BEFORE  
| AFTER  
]
>
<
[ INSERT  
| UPDATE  
| DELETE  
]
>

 ON  
<tableName 
>
//dbo代表该表的全部者 

 FOR EACH ROW 

 BEGIN 

--do something 

 END ; 
        
      

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

                         
           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表中。

                         
           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表发生更删改的时候，触发器启动了，运行了对应的操作，保证了数据的统一性。

很多其它文章请前往.

转载地址：http://zitna.baihongyu.com/

你可能感兴趣的文章