SQLite：相当于 ON DELETE LIMIT FOR "soft deletions"(表字段变为非 NULL)

Question

像往常一样，我正在实现一个；软删除"；模式(在SQLite数据库上(：永远不要删除磁盘上的任何内容，只需将其隐藏在应用程序中。

我的master表看起来像：

• id(INT(
• deleted(NULL或TEXT(，即NULL或ISO删除日期/时间

当我想"；删除"；实际上，我只是将其CCD_ 4字段设置为当前日期/时间。

我还有另一个存储关系的表references：

• id(INT，FOREIGN KEY master.id ON DELETE RESTRICT(
• ref(INT，FOREIGN KEY master.id ON DELETE RESTRICT(

意思是，id引用了ref，所以不能让它悬空。

显然，由于有了FOREIGN KEY，如果master表中的记录被任何references.id/ref引用，那么实际上就无法对其进行SQLDELETE，引擎会强制执行这一点。但如果可能的话，我想把这张支票扩展到"；软删除"；。

换句话说，当且仅当master.id列在references.id/ref中时，我希望禁止master.deleted字段的任何SQLUPDATE从NULL变为非NULL

到目前为止，在应用程序级别强制执行这一点已经足够满足我的需求了，但在这个项目上，我真的需要"皮带和吊带"；所以数据库层也应该强制执行它。我不知道如何开始解开它

Answer 1

正如@Serg所指出的，触发器解决了这个问题。

除了SQLite文档之外，本教程还极大地帮助我理解了它

以下是我的想法。这是我的第一次尝试，所以它可能会在性能方面得到增强，但功能就在这里：

CREATE TRIGGER master_soft_delete
BEFORE UPDATE OF deleted
ON master
WHEN (OLD.deleted IS NULL) AND (NEW.deleted IS NOT NULL)
BEGIN
SELECT
CASE
WHEN EXISTS(SELECT 1 FROM references AS r
WHERE (id = NEW.id) OR (ref = NEW.id) LIMIT 1)
THEN
RAISE(ABORT, 'master.id in references, aborting soft deletion')
END;
END;