Tell HN: SQLite3 does have something like stored procedures

Second, yeah, `last_insert_rowid()` doesn't work in this case, but what you might do is a) have a timestamp column and/or maybe a transaction ID column as well that you set in the INSERTs in the trigger body, b) maybe have audit/log tables that you insert into in those same trigger bodies, where you might use `last_insert_rowid()`.

Third, I urge you to think about why you care about the inserted row IDs. If you're doing INSERTs w/o `OR IGNORE`, then if your INSERT into the view succeeds then you know you can SELECT to see what IDs you got, then COMMIT. If you're doing `OR IGNORE`, then maybe you don't care whether you got IDs assigned just now or in the past, but you still want to know what IDs those rows have, so you select for them.

Lastly, I'd think about whether you want row IDs at all. I mean, they can be useful. And when it comes to things like "users", you generally want some sort of internal identifier other than the name. Like, if you rename users or roles often (but, you really should not allow that!) then using row IDs in the foreign keys means you don't have to worry about cascading renames. But you might be able to get away w/o role IDs. Also, you might find it better to have a single table for all "numeric ID" assignments, and then you could make your users and roles tables not have an INTEGER PRIMARY KEY, just the name as the PRIMARY KEY and a numeric ID column as a foreign key referencing that one single table.

E.g.,

  CREATE TABLE IF NOT EXISTS IDs
    (id INTEGER PRIMARY KEY,
     kind TEXT NOT NULL,
     name TEXT NOT NULL,
     UNIQUE (kind, name));
  
  CREATE TABLE IF NOT EXISTS user
    (name TEXT PRIMARY KEY,
     id INTEGER FOREIGN KEY REFERENCES IDs (id))
  WITHOUT ROWID;
  
  CREATE TABLE IF NOT EXISTS role
    (name TEXT PRIMARY KEY,
     id INTEGER FOREIGN KEY REFERENCES IDs (id))
  WITHOUT ROWID;

  - insert into IDs twice to get IDs for the user and role
  - insert into user
  - insert into role

  CREATE TABLE role_user (
    role_kind TEXT NOT NULL DEFAULT('role')
                            CHECK(role_kind = 'role'),
    user_kind TEXT NOT NULL DEFAULT('user')
                            CHECK(user_kind = 'user'),
    role_name TEXT, -- should be NOT NULL bc SQLite3
                    -- does not force that in PKs
    user_name TEXT, -- ditto
    PRIMARY KEY (role_name, user_name),
    FOREIGN KEY (role_kind, role_name)
        REFERENCES role(kind, name)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (user_kind, user_name)
        REFERENCES user(kind, name)
        ON DELETE CASCADE
        ON UPDATE CASCADE
  ) STRICT, WITHOUT ROWID;

  CREATE TRIGGER role_user_view_trigger
  INSTEAD OF INSERT ON role_user_view
  FOR EACH ROW
  BEGIN
  INSERT OR IGNORE INTO IDs(kind, name)
    VALUES('role', NEW.role_name),
    VALUES('user', NEW.user_name);
  INSERT OR IGNORE INTO role(name, id)
    SELECT NEW.role_name, id
    FROM IDs
    WHERE kind = 'role' AND name = NEW.role_name;
  INSERT OR IGNORE INTO user(name, id)
    SELECT NEW.user_name, id
    FROM IDs
    WHERE kind = 'user' AND name = NEW.user_name;
  INSERT OR IGNORE INTO role_user(role_name, user_name)
    VALUES(NEW.role_name, NEW.user_name);
  END;

Want to know the numeric IDs assigned to the users and roles possibly created this way? Just SELECT for them!