sqlite常用操作

CREATE VIRTUAL TABLE Media_Video_Resource_virtual USING fts5(id, keywords, resourceName,resourcePath);//创建虚拟表 并以fts5模块来进行全文检索

//新增触发器
CREATE TRIGGER Media_Video_Resource_insert AFTER INSERT ON Media_Video_Resource
BEGIN
    insert into Media_Video_Resource_virtual(id, keywords, resourceName,resourcePath) VALUES (NEW.id,NEW.keywords, NEW.resourceName,NEW.resourcePath);
END;

//变更触发器
CREATE TRIGGER Media_Video_Resource_update AFTER update ON Media_Video_Resource
BEGIN
    update Media_Video_Resource_virtual set keywords=NEW.keywords,resourceName=NEW.resourceName,resourcePath=NEW.resourcePath where id=NEW.id;
END;

//删除触发器
CREATE TRIGGER Media_Video_Resource_delete AFTER delete ON Media_Video_Resource
BEGIN
    delete from Media_Video_Resource_virtual where id=old.id;
END;

//查询所有触发器
SELECT * FROM sqlite_master WHERE type = 'trigger';

//删除指定触发器
DROP TRIGGER Media_Video_Resource_update;

//新增字段
ALTER TABLE Media_Video_Resource ADD COLUMN resourceVideoName VARCHAR(200)