SQLite Trigger – How to Get the rowid in a Trigger for Another Table

insertsqlitetrigger

Given

the below schema

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)
);

Used in client

e.g. Java equivalent prepared statements with ?s and params of these two statements:

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

Question

Is it possible to write a trigger (or anything else) without client code (only SQL) which achieves the above results with only

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

Note: Of course the client code will retrieve the new rowid for the inserted Container, but I don't care about the new (root) Item, that will be used much later by reading from Container.

The trigger would be along the lines of:

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;

Changing Container.root to NULL and creating an AFTER INSERT TRIGGER shouldn't be an option. But then I guess the same problem of getting the Item's rowid would still stand.

Best Answer

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version:

-- 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;

Output with comments:

---- 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)

Notes:

  • Using INSERT INTO Table VALUES format helps to catch schema modification bugs since when a new column is added the INSERT will fail instead of ignoring the new column.
  • One can still use the Container table to insert into.
  • last_insert_rowid() doesn't work when used after an INSERT INTO on a view with INSTEAD OF INSERT, i.e. after INSERT INTO Container_Auto, you'll have to requery the inserted row :(
Related Question