Use of sqlite3 triggers and sqlite3 triggers

Use of sqlite3 triggers and sqlite3 triggers

The storage of applications in the osx dock is studied ~ In the/Library/Application Support/Dock/next database file with a long name, I have briefly introduced the deletion of the osx launchpad icon. Here I have analyzed the db file.

In osx, the db file is the database file corresponding to the sqlite3 database. If you have learned andorid or ios, you should be familiar with it. You can view the database and see the database (the database file with a long name) the following table exists:
App_sources dbinfo image_cache widgets apps downloading_apps items categories groups widget_sources

The two most important tables need to count the apps and items tables, but the table is the most basic. The table structure is as follows:

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 & 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 dbi Nfo 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>

It can be seen from the above that items corresponds to many triggers, so items should be the base table. Let's start with the trigger.

When the data in the table in the database changes, including any insert, update, delete operations, if we write the corresponding DML trigger to the table, the trigger is automatically executed. DML triggers are mainly used to enforce business rules, expand SQL Server constraints, and default values. Because we know that the constraint can only constrain data in the same table, and the trigger can execute any SQL command.

The following example describes how to use a trigger. There are three tables in the database. src, backup, and del represent the original data table, backup data table, and delete data table, what we want to achieve now is to synchronize the insertion, update, and deletion of the original data to the backup table. to delete the original data, we need to write the deleted information to the del table, that is to say, to ensure that the src and backup tables are exactly the same at the same time, the del table stores the deleted information.

The trigger is created as follows:

1 2 3 4 5 6

Create trigger <[BEFORE | AFTER]> <[INSERT | UPDATE | DELETE]> ON <tableName> // dbo indicates the owner of the table. FOR EACH ROW BEGIN -- Do something END;

All three tables are very simple, with only one int type id attribute. Create a table and trigger as follows:

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;

The meaning of the above three triggers is still very easy to understand. We need to note that in the operations between begin and end, use new and old to point to the new and old data in the previous role table. For example, trigger1 is penalized after the src table is inserted. In this case, trigger places the new value (new. id) insert the table with 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); // insert data 1 Sqlite> insert into src values (2); // insert data 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; // update data Sqlite> select * from src; 1 3 Sqlite> select * from backup; 1 3 Sqlite> select * from del; Sqlite> delete from src where id = 1; // delete data Sqlite> select * from src; 3 Sqlite> select * from backup; 3 Sqlite> select * from del; 1 Sqlite>

It can be seen that when the src table is deleted and modified, the trigger is started and corresponding operations are executed to ensure data uniformity.

For more articles, go to xiaopengxuan.

Add a judgment statement to the SQLite trigger

If @ condition = 1
Update table_a set field_a = 'A' where exists (select 1 from OLD where cid = OLD. id)
Else
Update table_ B set field_a = 'B' where exists (select 1 from OLD where cid = OLD. id)

==>

Update table_a set field_a = 'A'
Where 1 = 1
And @ condition = 1
And exists (select 1 from OLD where cid = OLD. id)

Update table_ B set field_a = 'B'
Where 1 = 1
And @ condition! = 1
And exists (select 1 from OLD where cid = OLD. id)

Does sqlite3 support insert triggers?

Okay, I have misunderstood the meaning of the trigger. What I thought before was to judge before the insertion. If yes, insert the trigger, non-conforming: the official when is the content in the middle of the whin end to be executed. If not, the insert statement is always executed, but if you want to execute additional content (begin end), it depends on the when situation. The following is my solution: Determine whether the data to be inserted exists, if yes, delete the current one. Begin Transaction; Drop Trigger If Exists MAIN .; create Trigger MAIN. before insert On for each ROWwhen NEW. DIRE in (select dire from favline where linename = NEW. linename) begin delete from favline where linename = new. linename and dire = new. dire; end; Commit Transaction; view original post>