Demo using a View Trigger to insert data into multiple tables

insert_view.sql

-- https://news.ycombinator.com/item?id=31913062
-- https://sqlite.org/forum/forumpost/cd05f203c94e6458

-- tables

CREATE TABLE user (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNIQUE NOT NULL
) STRICT;

CREATE TABLE role (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNIQUE NOT NULL
) STRICT;

CREATE TABLE role_user (
    role_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, user_id)
) STRICT;

-- View

CREATE VIEW role_user_view AS
SELECT 
    role.name as role_name,
    user.name AS user_name, 
    role_user.created_at
FROM
    user JOIN
    role_user ON user.id = role_user.user_id JOIN
    role ON role_user.role_id = role.id
;

-- trigger

CREATE TRIGGER role_user_view_trigger
INSTEAD OF INSERT ON role_user_view
FOR EACH ROW
BEGIN
INSERT INTO role(name) VALUES(new.role_name);
INSERT INTO user(name) VALUES(new.user_name);
INSERT INTO role_user(role_id, user_id, created_at) VALUES(
    (SELECT id FROM role WHERE name = new.role_name),
    (SELECT id FROM user WHERE name = new.user_name),
    "now"
);
END;

INSERT INTO role_user_view(role_name, user_name) VALUES ("admin", "bob");

-- test queries

SELECT * FROM role_user_view;

-- ┌───────────┬───────────┬────────────┐
-- │ role_name │ user_name │ created_at │
-- ├───────────┼───────────┼────────────┤
-- │ admin     │ bob       │ now        │
-- └───────────┴───────────┴────────────┘

SELECT * FROM user;

-- ┌────┬──────┐
-- │ id │ name │
-- ├────┼──────┤
-- │ 1  │ bob  │
-- └────┴──────┘