sqlite – 触发器中的INSERT或IGNORE

似乎大文件大小的主要原因是相同的字符串倾向于在数千行中重复.为了避免这个问题,我计划为字符串池创建另一个表,如下所示：

CREATE TABLE AddressLookup (
   ID      INTEGER PRIMARY KEY AUTOINCREMENT,Address TEXT UNIQUE
);

CREATE TABLE EmailInfo (
   MessageID   INTEGER PRIMARY KEY AUTOINCREMENT,ToAddrRef   INTEGER REFERENCES AddressLookup(ID),FromAddrRef INTEGER REFERENCES AddressLookup(ID)
   /* Additional columns omitted for brevity. */
);

为方便起见,加入这些表的视图：

CREATE VIEW EmailView AS
SELECT
   MessageID,A1.Address AS ToAddr,A2.Address AS FromAddr
FROM EmailInfo
   LEFT JOIN AddressLookup A1 ON (ToAddrRef = A1.ID)
   LEFT JOIN AddressLookup A2 ON (FromAddrRef = A2.ID);

为了能够像使用常规表一样使用此视图,我做了一些触发器：

CREATE TRIGGER trg_id_EmailView
   INSTEAD OF DELETE ON EmailView
BEGIN
   DELETE FROM EmailInfo WHERE MessageID = OLD.MessageID;
END;

CREATE TRIGGER trg_ii_EmailView
   INSTEAD OF INSERT ON EmailView
BEGIN
   INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.ToAddr);
   INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.FromAddr);
   INSERT INTO EmailInfo
      SELECT NEW.MessageID,A1.ID,A2.ID
      FROM AddressLookup A1,AddressLookup A2
      WHERE A1.Address = NEW.ToAddr AND A2.Address = NEW.FromAddr;
END;

CREATE TRIGGER trg_iu_EmailView
   INSTEAD OF UPDATE ON EmailView
BEGIN
   UPDATE EmailInfo SET MessageID = NEW.MessageID
      WHERE MessageID = OLD.MessageID;
   REPLACE INTO EmailView
      SELECT NEW.MessageID,NEW.ToAddr,NEW.FromAddr;
END;

问题

后：

INSERT OR REPLACE INTO EmailView VALUES (1,'alice@example.com','bob@example.com');
INSERT OR REPLACE INTO EmailView VALUES (2,'chad@example.com');

更新的行包含：

MessageID   ToAddr               FromAddr
---------   ------               --------
1           NULL                 bob@example.com
2           alice@example.com    chad@example.com

有一个不应该存在的NULL. EmailInfo表中的相应单元格包含孤立的ToAddrRef值.

如果您一次执行一个INSERT,您将看到AddressLookup表中的Alice的ID发生了变化！

看来这个行为是documented：

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire,then conflict handling policy of the outer statement is used instead.

因此,顶级“INSERT OR REPLACE”语句中的“REPLACE”将覆盖触发器程序中的关键“INSERT OR IGNORE”.

有没有办法让它按照我想要的方式工作？

解决方法

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!