Created
June 29, 2022 00:35
-
-
Save bbkane/ed385787f06efe4473f3cb7a3b32c8ac to your computer and use it in GitHub Desktop.
Demo using a View Trigger to insert data into multiple tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 │ | |
-- └────┴──────┘ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment