給定

下面的架構

CREATE TABLE Item (
   _id         INTEGER      NOT NULL,
   name        NVARCHAR     NOT NULL,
   parent      INTEGER          NULL
       CONSTRAINT fk_Recursive
           REFERENCES Item(_id),
   PRIMARY KEY(_id AUTOINCREMENT)
);
CREATE TABLE Container (
   _id         INTEGER      NOT NULL,
   name        NVARCHAR     NOT NULL,
   root        INTEGER      NOT NULL
       CONSTRAINT fk_Container_root
           REFERENCES Item(_id),
   PRIMARY KEY(_id AUTOINCREMENT)
);

在客戶端使用

例如，Java 等價的prepared statements 與?這兩個語句的s 和params：

INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);

題

是否可以在沒有客戶端程式碼（僅 SQL）的情況下編寫觸發器（或其他任何內容），僅通過

INSERT INTO Container(name) VALUES('My Box');

注意：當然，客戶端程式碼會檢索新rowid的插入的Container，但我不關心新的（根）Item，稍後將通過讀取從Container.

觸發器將是：

CREATE TRIGGER Rooter
INSTEAD OF INSERT ON Container WHEN (root IS NULL) BEGIN
   SET newRoot = INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
   INSERT INTO Container VALUES (new._id, new.name, newRoot);
END;

更改Container.root為NULL並創建一個AFTER INSERT TRIGGER不應該是一種選擇。但後來我猜想獲得Item‘s的同樣問題rowid仍然存在。

基於CL的建議再看一遍last_insert_rowid()，現在很明顯INSTEAD OF觸發器僅適用於VIEWs（請參閱問題的評論），這是一個工作版本：

-- We need a view to create the trigger on
CREATE VIEW Container_Auto AS select * from Container;

-- Since it's automatic we need to check if we need to create a root
CREATE TRIGGER Rooter_Auto
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NULL) BEGIN
   INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
   INSERT INTO Container VALUES (new._id, new.name, last_insert_rowid());
END;
-- If we don't need a root (i.e. the client supplied it), just pass the values through
CREATE TRIGGER Rooter_Transparent
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NOT NULL) BEGIN
   INSERT INTO Container VALUES (new._id, new.name, new.root);
END;

-- Tests
INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);
INSERT OR IGNORE INTO Container(name) VALUES('Won''t work');
INSERT INTO Container_Auto(name) VALUES('All Automatic');
INSERT INTO Container_Auto(_id, name) VALUES(100, 'Specific ID');
INSERT INTO Container_Auto(name, root) VALUES('Specific root', 1);

-- Results
SELECT '---- Container ----';
SELECT * from Container;
SELECT '---- Item ----';
SELECT * from Item;

帶有註釋的輸出：

---- Container ----
1|My Box|1          -- everything as we specified
                   -- note the OR IGNORE failed because root IS NULL
3|All Automatic|2   -- the failed insert incremented the rowid as well as created a new root
100|Specific ID|3   -- the supplied _id is used, new root is created
101|Specific root|1 -- the supplied root is used, _id is incremented
---- Item ----
1|ROOT|             -- created manually
2|ROOT|             -- created automatically by INTO Container_Auto(name)
3|ROOT|             -- created automatically by INTO Container_Auto(_id, name)

筆記：

• 使用INSERT INTO Table VALUES格式有助於擷取模式修改錯誤，因為當添加新列時，INSERT它將失敗而不是忽略新列。
• 仍然可以使用Container表插入。
• last_insert_rowid()``INSERT INTO在視圖上使用時不起作用INSTEAD OF INSERT，即在之後INSERT INTO Container_Auto，您必須重新查詢插入的行:(

引用自：https://dba.stackexchange.com/questions/73703