我们在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