No relation with Qt but need help to update few rows in SQLITE when delete one row

filipdns

Hello, I know that is not really QT question but here many people have good knowledge, then I ask here.

I'm using qt with locale storage and I would like to have my column total-time updated if I delete one row by the time_use column.
I explain:
Column:
Date , time_use, total_time
when I add row, the total_time is previous total_time + new time_use
Then when I delete one row
I want to have total_time of each upper rows - time_use deleted.
thank you for your help

VRonin

if db.driver()->hasFeature(QSqlDriver::EventNotifications) returns true (as it should) then you can create a trigger in the database so then you can use QSqlDriver::subscribeToNotification and the QSqlDriver::notification signal to recalculate the total column

JonB

@VRonin
I know nothing about SQLite/"local storage", but by the time one is writing a trigger can't that do the recalculation and update of other rows without needing to to pass "notifications" to Qt client code to do that work?

filipdns

thanks VRonin,

I don't understand anything about your suggestion but thank you very much for your time for me.

I'm using qml + js to work with SQLite, not cpp.

kind regards
Philippe

VRonin

As suggested by @JonB you don't need to do anything in C++, QML or js. Just open your database using a program like http://sqlitebrowser.org/ and execute the SQL query:
(mytable is the name of your table, blablabla is a select that calculates the cumulative sum and depends on the structure of mytable. Just google "sqlite cumulative sum")

CREATE TRIGGER IF NOT EXISTS updateTime AFTER DELETE ON  mytable 
BEGIN
UPDATE mytable SET total_time= blablabla
END;

Buckwheat

@JonB

Very good observation. Triggers seem to be under utilized in SQLite. This would actually be a preferred way to update summary calculations. Imagine if you were doing this across a server. The back-and-forth traffic would be so inefficient. I have used triggers for years in SQLite without incident. I even use them for housekeeping modifications to tables.

Views are another great tool to coalesce data from relation tables for display.