如何在SQLite触发器中实现If语句?



我们在SQLite数据库中定义了一个自引用关系…

Create Table Categories{
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL CHECK(length(Text) > 0),
    ParentId INTEGER References Categories(Id),
    IsSelected BOOL
};

我们正在尝试在IsSelected字段上设置一个触发器,以自动将值沿层次结构向上传播。

当将值设置为True时,很容易。我们只需将触发器设置为将其父节点设置为True,然后将其父节点设置为True,沿着层次结构往上走,直到到达根节点。

当我们将其设置为False时,这个逻辑就不适用了,因为可能有另一个被修改的记录的兄弟仍然为真,所以它的父记录仍然为真。

那么如何在触发器中执行以下操作呢?注意,这显然是SQL, c#和LINQ的无意义混合,但它说明了我想要做的。

CREATE TRIGGER T_Categories_IsSelectedChanged
UPDATE OF IsSelected ON Categories
BEGIN
    if(new.IsSelected)
        Update Categories Set IsSelected = True Where Id = old.ParentId;
    else
    {
        void isSiblingSelected = Categories
            .Where(c => c.ParentId = old.ParentId)
            .Any(c => c.IsSelected);
        UPDATE Categories 
        SET IsSelected = isSiblingSelected
        WHERE Id = old.ParentId;
    }
END

从技术上讲,'Else'子句在两种情况下都有效。If更像是一个短路,但也许我可以做这样的事情…

Update Categories
SET IsSelected = (
    SELECT EXISTS(SELECT 1 FROM Categories
    WHERE IsSelected = True 
    AND ParentId = old.ParentId
    LIMIT 1))
Where ID = old.ParentId;

…但这只是猜测。我的思路对吗?

CASE不是语句;您只能在表达式中使用它。

然而,触发器有一个WHEN子句,它接受一个任意的SQL表达式:

CREATE TRIGGER T_Categories_IsSelectedChanged_true
AFTER UPDATE OF IsSelected ON Categories
FOR EACH ROW
WHEN new.IsSelected
BEGIN
    UPDATE Categories
    SET IsSelected = 1
    WHERE Id = old.ParentId;
END;
CREATE TRIGGER T_Categories_IsSelectedChanged_false
AFTER UPDATE OF IsSelected ON Categories
FOR EACH ROW
WHEN NOT new.IsSelected
BEGIN
    UPDATE Categories 
    SET IsSelected = (SELECT IFNULL(MAX(IsSelected), 0)
                      FROM Categories
                      WHERE ParentId = old.ParentId)
    WHERE Id = old.ParentId;
END;

您可以参考http://www.sqlite.org/lang_expr.html#case.

我不确定,但你的代码可能像,

CREATE TRIGGER T_Categories_IsSelectedChanged
UPDATE OF IsSelected ON Categories
BEGIN
case when new.isSelected then
    Update Categories Set IsSelected = True Where Id = old.ParentId;
else
    Update Categories Set IsSelected = Categories
        .Where(c => c.ParentId = old.ParentId)
        .Any(c => c.IsSelected);
END

相关内容

  • 没有找到相关文章

最新更新