SQLite AFTER DELETE触发器违反约束仍触发的问题解决问询

问题背景 #

我开发的Python GUI应用基于SQLite3，通过AFTER INSERT/UPDATE/DELETE触发器调用自定义函数触发数据变更事件。但遇到异常行为：当删除操作因外键约束被阻止时，AFTER DELETE触发器仍会触发事件，导致软件误以为实体已被删除，后续出现逻辑错误。

具体场景：

• 启用PRAGMA foreign_keys = 1，被引用的companies实体在有people关联时无法删除；
• 自定义BEFORE触发器执行失败时，AFTER触发器不会触发，这部分逻辑正常；
• 外键约束阻止删除时，AFTER触发器仍会执行，执行顺序为：DELETE语句执行→行被标记删除→AFTER触发器触发事件→外键约束检查失败→事务回滚→软件已错误响应删除事件。

原因分析 #

SQLite的AFTER触发器是在行级修改完成后、语句级约束检查（如外键约束）之前执行的。当外键约束检查失败导致事务回滚时，AFTER触发器已经完成执行，因此自定义函数会被误调用。

可行解决方案 #

方案1：用BEFORE DELETE触发器提前检查外键约束 #

在BEFORE触发器中手动检查是否存在外键引用，若存在则直接抛出异常终止操作，这样AFTER触发器就不会被触发。

示例SQL：

-- 前置检查触发器：存在外键引用则终止删除
CREATE TRIGGER IF NOT EXISTS trigger_companies_before_delete
BEFORE DELETE ON companies
BEGIN
  IF EXISTS (SELECT 1 FROM people WHERE company_id = OLD.ID) THEN
    RAISE(ABORT, 'FOREIGN KEY constraint failed');
  END IF;
END;

-- 仅在真正删除成功时触发事件的AFTER触发器
CREATE TRIGGER IF NOT EXISTS trigger_companies_after_delete
AFTER DELETE ON companies
BEGIN
  SELECT my_func_entity_deleted("companies", OLD.ID);
END;

方案2：改用INSTEAD OF DELETE触发器 #

INSTEAD OF触发器会替代原有的DELETE操作，在触发器内先检查外键约束，确认可以删除后再执行删除并触发事件。

示例SQL：

CREATE TRIGGER IF NOT EXISTS trigger_companies_delete
INSTEAD OF DELETE ON companies
BEGIN
  -- 检查是否无关联引用
  IF NOT EXISTS (SELECT 1 FROM people WHERE company_id = OLD.ID) THEN
    -- 执行实际删除
    DELETE FROM companies WHERE ID = OLD.ID;
    -- 触发变更事件
    SELECT my_func_entity_deleted("companies", OLD.ID);
  ELSE
    -- 抛出与原外键约束一致的错误
    RAISE(ABORT, 'FOREIGN KEY constraint failed');
  END IF;
END;

方案3：Python层面捕获异常后修正事件逻辑 #

如果不想修改SQL触发器，可以在Python代码中，仅当DELETE语句执行无异常且事务提交成功时，才触发对应的变更事件。但这种方法无法覆盖直接执行SQL语句的场景（如其他模块或工具执行的SQL），仅适用于所有数据库操作都通过统一Python接口的情况。

示例Python代码片段：

try:
    conn.execute("DELETE FROM companies WHERE ID = 1")
    conn.commit()
    # 仅提交成功时触发事件
    my_func_entity_deleted("companies", 1)
except sqlite3.IntegrityError:
    print("DELETE failed")
    # 不触发事件

验证示例（方案1） #

修改原Python测试代码，添加BEFORE触发器后执行：

import sqlite3

conn = sqlite3.connect(":memory:")

def test_function(scope, id_: int):
    print("--- delete function:", "DELETE TRIGGER called user defined function", "(", scope, id_, ")")
    res = conn.execute("SELECT * FROM companies").fetchall()
    print("--- delete function:", "SELECT in trigger returns", res)

conn.create_function("emitDelete", 2, test_function)
conn.execute("PRAGMA foreign_keys = 1;")
conn.execute("""
CREATE TABLE companies(
    ID integer primary key,
    name text not null unique
);
""")
conn.execute("""
CREATE TABLE people(
    ID integer primary key,
    company_id integer not null references companies,
    name text not null,
    UNIQUE(company_id, name)
);
""")
conn.execute("INSERT INTO companies(name) VALUES('testComp');")
conn.execute("INSERT INTO people(company_id, name) VALUES(1, 'testPersA');")

# 添加BEFORE检查触发器
conn.execute("""
CREATE TRIGGER IF NOT EXISTS trigger_companies_before_delete
BEFORE DELETE ON companies
BEGIN
  IF EXISTS (SELECT 1 FROM people WHERE company_id = OLD.ID) THEN
    RAISE(ABORT, 'FOREIGN KEY constraint failed');
  END IF;
END;
""")

# 保留AFTER触发器
conn.execute("""
CREATE TRIGGER IF NOT EXISTS test_trigger 
AFTER DELETE ON companies
BEGIN
SELECT emitDelete('companies', OLD.ID);
END;
""")

if __name__ == "__main__":
    print("DELETE initiated")
    try:
        conn.execute("DELETE FROM companies WHERE ID = 1")
        print("DELETE finished")
    except sqlite3.IntegrityError:
        print("DELETE failed")
    print("SELECT after finishing delete trigger and statement:",
          conn.execute("SELECT * FROM companies").fetchall())

执行输出：

DELETE initiated
DELETE failed
SELECT after finishing delete trigger and statement: [(1, 'testComp')]

可见自定义函数未被调用，避免了误触发事件。

内容的提问来源于stack exchange，提问作者h-c