how to use "dbstat" table in a trigger in sqlite?

user_8440663 0
Viewed 126

i need to limit my table size to suppose 10 Mb using dbstat i want to create a trigger for the same but iam getting below error SQL error: unsafe use of virtual table "dbstat"

below is the trigger query:

CREATE TRIGGER less_den_ten_mb BEFORE INSERT ON mqtt6 BEGIN DELETE FROM mqtt6 WHERE timestamp=(select min(timestamp) from mqtt6 where 1=(SELECT sum(pgsize)>1024000 from dbstat where name="mqtt6")); end;

sqlite
1 Answers

If you look at the source for the virtual table in its statConnect() function you'll see

sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY);

And from deep in the documentation:

Calls of the form sqlite3_vtab_config(db,SQLITE_VTAB_DIRECTONLY) from within the the xConnect or xCreate methods of a virtual table implementation prohibits that virtual table from being used from within triggers and views.

So you're not allowed to use dbstat in a trigger. This is part of a security feature added in sqlite 3.31.0. There are further details on the rationale and reasoning if you're interested.

user_99521960
Related