CODEWITHSUNDEEP
sqlsqliterecursiontriggers
aaazalea
aaazalea

Reputation: 7920

How do I prevent Trigger recursion in SQLite?

I am trying to create a trigger which will update the grades of all the friends of a student whose grade is updated.

create trigger upgrade
after update on Highschooler
when new.grade=old.grade+1 and trigger_nestlevel() < 2
begin
    update Highschooler
    set grade=new.grade
    where ID in (
      select ID2
      from Friend
       where ID1=old.ID
    )
;
end

friends of friends (of friends...) of the person whose grade is increased are also being 'upgraded' how can i stop this?

Upvotes: 1

Views: 2356

Answers (1)

Bill
Bill

Reputation: 11832

As you can read in Limits In SQLite you can clear the recursive trigger capability by using the PRAGMA recursive_triggers statement.

10. Maximum Depth Of Trigger Recursion

SQLite limits the depth of recursion of triggers in order to prevent a statement involving recursive triggers from using an unbounded amount of memory.

Prior to SQLite version 3.6.18, triggers were not recursive and so this limit was meaningless. Beginning with version 3.6.18, recursive triggers were supported but had to be explicitly enabled using the PRAGMA recursive_triggers statement. Beginning with version 3.7.0, recursive triggers are enabled by default but can be manually disabled using PRAGMA recursive_triggers. The SQLITE_MAX_TRIGGER_DEPTH is only meaningful if recursive triggers are enabled.

The default maximum trigger recursion depth is 1000.

Upvotes: 4

Related Questions