By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE members (
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
LVL INTEGER NOT NULL,
Notes TEXT
);
✓
INSERT INTO members (Name,LVL,Notes) VALUES
('Jean',12,'First stage'),
('Jacques',1,'Second stage'),
('Amelie',1,'Second stage'),
('Louis',13,'Some other note altogether');
✓
SELECT * FROM members;
| Id | Name | LVL | Notes |
|---|---|---|---|
| 1 | Jean | 12 | First stage |
| 2 | Jacques | 1 | Second stage |
| 3 | Amelie | 1 | Second stage |
| 4 | Louis | 13 | Some other note altogether |
CREATE TABLE members_changes (
timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
Id INTEGER REFERENCES members(Id),
Name TEXT NOT NULL,
LVL INTEGER NOT NULL,
Notes TEXT
);
✓
CREATE TABLE tmp (
Name TEXT NOT NULL,
LVL INTEGER NOT NULL,
Notes TEXT
);
✓
CREATE TRIGGER IF NOT EXISTS tr_insert_tmp AFTER INSERT ON tmp
BEGIN
INSERT INTO members_changes(Id,Name,LVL,Notes)
SELECT Id,Name,LVL,Notes
FROM members
WHERE Name = NEW.NAME AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
UPDATE members
SET LVL = NEW.LVL, Notes = NEW.Notes
WHERE Name = NEW.Name AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
END;
✓
INSERT INTO tmp (Name,LVL,Notes) VALUES
('Jean',13,'First stage'),
('Jacques',1,'Second stage'),
('Amelie',1,'Third stage'),
('Louis',14,'Fourth stage');
✓
SELECT * FROM tmp;
| Name | LVL | Notes |
|---|---|---|
| Jean | 13 | First stage |
| Jacques | 1 | Second stage |
| Amelie | 1 | Third stage |
| Louis | 14 | Fourth stage |
SELECT * FROM members_changes;
| timestamp | Id | Name | LVL | Notes |
|---|---|---|---|---|
| 2024-12-12 14:56:58 | 1 | Jean | 12 | First stage |
| 2024-12-12 14:56:58 | 3 | Amelie | 1 | Second stage |
| 2024-12-12 14:56:58 | 4 | Louis | 13 | Some other note altogether |
SELECT * FROM members;
| Id | Name | LVL | Notes |
|---|---|---|---|
| 1 | Jean | 13 | First stage |
| 2 | Jacques | 1 | Second stage |
| 3 | Amelie | 1 | Third stage |
| 4 | Louis | 14 | Fourth stage |