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.
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 -
if
db.driver()->hasFeature(QSqlDriver::EventNotifications)
returns true (as it should) then you can create a trigger in the database so then you can useQSqlDriver::subscribeToNotification
and theQSqlDriver::notification
signal to recalculate the total column -
if
db.driver()->hasFeature(QSqlDriver::EventNotifications)
returns true (as it should) then you can create a trigger in the database so then you can useQSqlDriver::subscribeToNotification
and theQSqlDriver::notification
signal to recalculate the total column -
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 ofmytable
. Just google "sqlite cumulative sum")CREATE TRIGGER IF NOT EXISTS updateTime AFTER DELETE ON mytable BEGIN UPDATE mytable SET total_time= blablabla END;
-
@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?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.