No relation with Qt but need help to update few rows in SQLITE when delete one row
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.
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
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::notificationsignal to recalculate the total column
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?
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.
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:
mytableis the name of your table,
blablablais 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;
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.