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>