Use sqlite triggers to automate some database operations

Tips & Tutorials
1

If you followed my sqlite and Keyboard Maestro series, you (hopefully) learned quite a bit about how to integrate the two. The more time I spend with sqlite databases, the more I like the power they bring to KM.

I recently updated my The Decrufter macro to use a database to store a user-defined number of decrufted URLs. Previous versions of the macro used a global variable for this, but it was cumbersome to work with (lots of regex), took up memory, and was fragile.

It was, however, very easy to treat the variable like a stack with a limit: Prepend the newest decrufted URL, check the line count, and if it's over the limit, remove the last n lines to reach the limit.

I was trying to figure out how to do this with the database; I thought I'd have to do a record count, then delete any records over the limit. In chatting with ChatGPT, however, I learned about something new to me: sqlite triggers.

A trigger is a set of database actions that act automatically on INSERT, UPDATE, or DELETE commands. Using triggers, it turned out to be really easy to limit the size of my decrufted URLs table:

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON Decrufted
BEGIN
    DELETE FROM Decrufted
    WHERE ROWID IN (
        SELECT ROWID
        FROM Decrufted
        ORDER BY ROWID DESC
        LIMIT -1 OFFSET 150
    );
END

You enter the trigger commands just one time (I do so in the macro when the database is first create), and they then operate automatically whenever one of the monitored events happens—in this case, that's the INSERT trigger.

The commands then look at the special ROWID field, which is an automatically-created value associated with each row of the table. Because new records are inserted at the end, the above code orders the ROWID field in descending order, and then deletes anything past row 150—that is, the oldest entries over the limit.

Because this is probably only of interest to a tiny number of people, I won't go into more detail here. But if you want to see how it looks, check out The Decrufter macro—you'll find there's a second trigger on INSERT that prevents the creation of duplicate records. (Look in 94] Setup to find the SQL.)

Not sure how I didn't know about triggers before, but I'm glad I know now!

-rob.

3 Likes