By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table document (id, type, title, path)
✓
INSERT INTO document VALUES (1, 'type1', 'title1', 'path1')
✓
create table DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
✓
CREATE TRIGGER document_handler AFTER UPDATE ON document
FOR EACH ROW
BEGIN
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'type', OLD.type, NEW.type, CURRENT_TIMESTAMP
WHERE NEW.type IS NOT OLD.type;
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'title', OLD.title, NEW.title, CURRENT_TIMESTAMP
WHERE NEW.title IS NOT OLD.title;
INSERT INTO DocumentUpdates (id_document, attribute_changed, lastvalue, newvalue, modification_date)
SELECT NEW.id, 'path', OLD.path, NEW.path, CURRENT_TIMESTAMP
WHERE NEW.path IS NOT OLD.path;
END;
✓
UPDATE document SET type = 'other type'
✓
UPDATE document SET title = 'other title'
✓
UPDATE document SET path = 'other path'
✓
SELECT * FROM DocumentUpdates
| id_document | attribute_changed | lastvalue | newvalue | modification_date |
|---|---|---|---|---|
| 1 | type | type1 | other type | 2025-03-09 07:33:15 |
| 1 | title | title1 | other title | 2025-03-09 07:33:15 |
| 1 | path | path1 | other path | 2025-03-09 07:33:15 |
UPDATE document SET type = 'another type', title = 'another title', path = 'other path';
✓
SELECT * FROM DocumentUpdates
| id_document | attribute_changed | lastvalue | newvalue | modification_date |
|---|---|---|---|---|
| 1 | type | type1 | other type | 2025-03-09 07:33:15 |
| 1 | title | title1 | other title | 2025-03-09 07:33:15 |
| 1 | path | path1 | other path | 2025-03-09 07:33:15 |
| 1 | type | other type | another type | 2025-03-09 07:33:15 |
| 1 | title | other title | another title | 2025-03-09 07:33:15 |