如何防止SQLite中的触发器递归？ [英] 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?

推荐答案

您可以在限制在SQLite中，您可以使用 PRAGMA recursive_triggers 语句来清除递归触发器功能。

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

10。触发器递归的最大深度

SQLite限制了触发器的递归深度，以防止涉及递归触发器的
语句使用无限量
的内存。

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

在SQLite 3.6.18版之前，触发器不是递归的，因此
这个限制是没有意义的。从版本3.6.18开始，支持递归
触发器，但必须使用
PRAGMA recursive_triggers 语句。从版本3.7.0开始，默认情况下启用
递归触发器，但可以使用 nofollow> PRAGMA recursive_triggers 。如果启用了递归触发器，则SQLITE_MAX_TRIGGER_DEPTH仅
有意义。

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.

默认最大触发器递归深度为1000。

The default maximum trigger recursion depth is 1000.

这篇关于如何防止SQLite中的触发器递归？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！