SQLite Forum

can we use a for/while loop inside sqlite triggers without sqlite trigger recursion ?



i was able to do looping via "PRAGMA recursive-triggers = true" but its less efficient than simple for loop in cpp code, so is there any for/while loop in sqlite trigger


CREATE TRIGGER less_den_ten_mb_after_insert 
AFTER INSERT ON mqtt6 
when (SELECT sum(size_t)>10240000 from mqtt6)
BEGIN 
DELETE FROM mqtt6 
WHERE timestamp=(select min(timestamp) from mqtt6); 
end;

CREATE TRIGGER less_den_ten_mb_delete 
after DELETE on mqtt6 
when (SELECT sum(size_t)>10240000 from mqtt6) 
BEGIN DELETE from mqtt6 WHERE timestamp=(SELECT min(timestamp) from mqtt6); 
END;

PRAGMA recursive_triggers = 1;