how to use dbstat table in a trigger?

(1.1) Originally by lokakit (loki1993) with edits by Richard Hipp (drh) on 2021-06-08 12:09:11 from 1.0 [link] [source]

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;

(2) By Richard Hipp (drh) on 2021-06-08 11:59:44 in reply to 1.0 [source]

The DBSTAT virtual table is marked with SQLITE_VTAB_DIRECTONLY, meaning that it cannot be used inside of triggers or views for security reasons. I don't recall what those security reasons are, right this moment. I should have put that information in a comment, I suppose. DBSTAT is read-only, so how could it present a security risk? Perhaps the DIRECTONLY designation was a mistake. But, following the principle of Chesterton's fence, I'm not going to remove that restriction until I understand why it is there.