create trigger demo 轉

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 >