databasesqlitetriggersbackendexpirationhandler

Preferred way to approach expired records delition in SQLite

There are 3 tables in the database -- Students, Courses, Professors. Each student has activation_deadline column, which is set to NULL upon activation. I need some mechanism that will periodically delete students who's activation_deadline is overdue (and prevent already 'expired' students from being activated).

At the moment I do it via three separate triggers (as there are no database or server level triggers in SQLite) for Students table.

One for UPDATE:

CREATE TRIGGER Remove_Unactivated_Students_Update
BEFORE UPDATE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

One for INSERT:

CREATE TRIGGER Remove_Unactivated_Students_Insert
BEFORE INSERT
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

And one for DELETE:

CREATE TRIGGER Remove_Unactivated_Students_Delete
AFTER DELETE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

Another approach would be to add some code to the backend, which will check and remove expired records before any other query to database is executed (though, it will increase amount of database calls, and this is not good).

Which (keeping 'expired' records removing logic in database trigger or backend) approach in this circumstances is preferred and why? What are the pitfalls and advantages of each?

Solution

  • SQLite is a serverless DBMS and you can't define/schedule tasks or jobs.

    Your requirement should be taken care of at the application level, where you can define a daily or weekly job to delete expired students.

    This involves the execution of only 1 very simple and fast DELETE statement, once per day/week:

    DELETE FROM Students WHERE activation_deadline <= strftime('%s', 'now');

    Note that the condition activation_deadline IS NOT NULL is covered by activation_deadline <= strftime('%s', 'now'), so it is not needed.

    Any solution involving multiple triggers is out of the question, because it would add unnecessary overhead to any simple INSERT/DELETE/UPDATE operation on the table.