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 use QSqlDriver::subscribeToNotification and the QSqlDriver::notification signal to recalculate the total column



  • @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?



  • 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



  • 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;
    


  • @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.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.