SQLite Forum

get changed columns data (previous/ after) on update row and previous data before delete row
Login

get changed columns data (previous/ after) on update row and previous data before delete row

(1) By RomiG (RomioGeek) on 2020-09-16 13:22:57 [source]

I am looking for possible approaches can be used to fetch only changed columns names data (previous/ after) on row update and complete previous data before row delete operation. I was looking at some references where triggers and update hook can be used to do so. On trying update hook, I noticed that only the row ID will be sent on insert/ update/ delete operation but that doesn't serve the purpose. Looking for recommendation on how to achieve "fetch only changed columns names data (previous/ after) on row update and complete previous data before row delete operation." and if possible some examples on how that approach can be used.

(2) By David Raymond (dvdraymond) on 2020-09-16 13:48:04 in reply to 1 [link] [source]

I don't really know the C side of things, but according to The pre-update hook page, then once you're inside the registered callback you can use the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions to get the old and new values of all the columns, and then decide what to do with them from there.

(3) By David Raymond (dvdraymond) on 2020-09-16 14:53:28 in reply to 1 [link] [source]

Triggers meanwhile are good if you want to do basic SQL with the changed values, like insert them into an auditing table. You can set up an on update trigger on each field to insert into an auditing table the field name, change time, old and new values, etc. Or an on delete trigger to insert all the old fields into a delete table along with the time.

With triggers it's all going to be handled by the SQLite engine in the background and none of the values or info will be sent back to the controlling program. It will basically be "well, you left me instructions on what to do on a delete, so I did it. No need to bug you every time about it."


--Not necessarily the best way to do it, but an example.

create table table1 (a text, b int, c float);

create table table1_audit
(change_time, change_type, old_a, old_b, old_c, new_a, new_b, new_c);

create trigger trg_table1_deletes
before delete on table1
begin
insert into table1_audit (change_time, change_type, old_a, old_b, old_c)
values (datetime('now'), 'delete', old.a, old.b, old.c);
end;

create trigger trg_table1_changes --only the fields that changed
before update on table1
begin
insert into table1_audit
values (datetime('now'), 'update',
case when new.a is not old.a then old.a end,
case when new.b is not old.b then old.b end,
case when new.c is not old.c then old.c end,
case when new.a is not old.a then new.a end,
case when new.b is not old.b then new.b end,
case when new.c is not old.c then new.c end);
end;


sqlite> insert into table1 values ('one', 1, 1.0), ('two', 2, 2.0);

sqlite> delete from table1 where b = 1;

sqlite> update table1 set c = 2.1 where b = 2;

sqlite> update table1 set a = 'three', b = 3, c = 3.0 where b = 2;

sqlite> select * from table1;
a|b|c
three|3|3.0

sqlite> select * from table1_audit;
change_time|change_type|old_a|old_b|old_c|new_a|new_b|new_c
2020-09-16 14:31:10|delete|one|1|1.0|||
2020-09-16 14:31:20|update|||2.0|||2.1
2020-09-16 14:31:37|update|two|2|2.1|three|3|3.0

sqlite>

(4) By RomiG (RomioGeek) on 2020-09-16 16:08:17 in reply to 3 [link] [source]

Thanks David for really quick help. I will try the options you mentioned above and based on the usability and performance will take call on which one to choose. Thank you!!