2

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.

5
2

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 :(
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.