Search code examples
shellloggingsqlitetail

sqlite trigger output

I'm trying to create a script that will act like tail -f but for sqlite files. Thinking that triggers were the best answer I tried the following:

CREATE TEMPORARY TRIGGER "tailf" AFTER INSERT ON "my_table"
  BEGIN
    SELECT * FROM "my_table" WHERE rowid = NEW.rowid;
  END;

The idea being that on insert, it would print the new row(s) to stdout. Unfortunately, when inserting into the table, I got nothing.

What am I doing wrong?

Also, assuming I can get this to work all from the sqlite shell, would it still work when other processes insert into the table?

Solution

  • When SQLite executes a SELECT command, it just returns the data to the application. It is the responsibility of the application to do something with the data (such as printing it in the case of the sqlite3 shell).

    Inside the trigger, there is no application that waits for the result of the SELECT, so the result is thrown away. The only purpose of SELECT commands inside a trigger is to call user-defined functions that have some side effect.

    In theory, you could define your own user-defined function to print something:

    CREATE TEMPORARY TRIGGER "tailf" AFTER INSERT ON "my_table"
BEGIN
    SELECT print(column1, column2, ...) FROM "my_table" WHERE rowid = NEW.rowid;
END;

    But unless you control those other processes, they will not install that function for you.