SQLite 3.27

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
2023-08-08 18:16:54

1
title
title1
other title
2023-08-08 18:16:54

1
path
path1
other path
2023-08-08 18:16:54


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
2023-08-08 18:16:54

1
title
title1
other title
2023-08-08 18:16:54

1
path
path1
other path
2023-08-08 18:16:54

1
type
other type
another type
2023-08-08 18:16:54

1
title
other title
another title
2023-08-08 18:16:54

id_document	attribute_changed	lastvalue	newvalue	modification_date
1	type	type1	other type	2023-08-08 18:16:54
1	title	title1	other title	2023-08-08 18:16:54
1	path	path1	other path	2023-08-08 18:16:54