CODEWITHSUNDEEP
sqliterecursion
mssqlw
mssqlw

Reputation: 73

Sqlite rowid > too many levels of trigger recursion, but trigger works fine?

I havw a table: MYTABLE(ID int);

I am using this query to generate N numbers of rowids in mytable:

  create trigger mytrigger after insert on MYTABLE
  when new.id < 1000 
  begin
  insert into MYTABLE select max(id)+1 from MYTABLE;
  end;
  insert into MYTABLE values (1);

It works fine, sqlite would generate me a rowid from 1 to 1000.

But when I subsitute:

when new.id < 1000

with larger number like: 

when new.id < 10000000

I receive an error: too many levels of trigger recursion

Now my question is, what's the point of a trigger if it can not handle at least one million options? Is there any way to solve that or should i just go and insert each row by myself:)

Upvotes: 0

Views: 1021

Answers (1)

CL.
CL.

Reputation: 180161

Triggers are not meant to have an arbitrary level of recursion.

The mechanism for arbitrary recursions are recursive common table expressions:

INSERT INTO MyTable(id)
WITH RECURSIVE n(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 1000
)
SELECT i FROM n;

Upvotes: 1

Related Questions