Android SQLite 更新触发器

发布于 2024-11-09 20:50:36 字数 1179 浏览 0 评论 0原文

我在 Android 中使用 SQLite 时遇到了一些问题。由于我的应用程序支持 Android 1.6，因此我无法使用外键，因此编写了触发器来强制执行这些约束。

我的两个表是车辆和燃料使用。 Vehicle 和fuel_use 都有一个registration_number 列。我的触发器确保当fuel_use中有条目引用车辆中的registration_number时，我无法更新车辆中的registration_number：

        //on update vehicle
    db.execSQL("CREATE TRIGGER fk_update_vehicle_trigger_fuel_references_vehicles_reg_no " +
            "BEFORE UPDATE ON " + VEHICLE_TABLE_NAME +
            " FOR EACH ROW BEGIN " +
            "SELECT RAISE(ROLLBACK, 'update on table " + VEHICLE_TABLE_NAME + " violates foreign key constraint fk_update_vehicle_trigger_fuel_references_vehicles_reg_no') " +
            "WHERE (SELECT " + REGISTRATION_NO_COLUMN + " FROM " + FUEL_USE_TABLE_NAME +
            " WHERE " + REGISTRATION_NO_COLUMN + " = OLD." + REGISTRATION_NO_COLUMN + ") IS NOT NULL; " +
            "END;");

这可以正常工作，因为它会停止更新，从而使fuel_use行引用不存在的车辆行。但是，我刚刚发现一个问题 - 当我尝试更新车辆中的不同列 - 初始里程 - 此触发器不允许它并强制回滚。我理解为什么会发生这种情况，但不知道如何重写触发器，因此只有在 Registration_number 列上进行更新才会强制回滚。我已经尝试过：

BEFORE UPDATE ON " + VEHICLE_TABLE_NAME + "." + REGISTRATION_NO_COLUMN

但这不起作用。事实上抛出了一个异常。

任何帮助将不胜感激。

原文

I'm having a bit of trouble with SQLite in Android. As my application supports Android 1.6, I am unable to use foreign keys, so have written triggers to enforce these constraints.

My two tables are vehicle and fuel_use. vehicle and fuel_use both have a registration_number column. My trigger ensures that I cannot update registration_number in vehicle when there are entries in fuel_use referencing registration_number in vehicle:

        //on update vehicle
    db.execSQL("CREATE TRIGGER fk_update_vehicle_trigger_fuel_references_vehicles_reg_no " +
            "BEFORE UPDATE ON " + VEHICLE_TABLE_NAME +
            " FOR EACH ROW BEGIN " +
            "SELECT RAISE(ROLLBACK, 'update on table " + VEHICLE_TABLE_NAME + " violates foreign key constraint fk_update_vehicle_trigger_fuel_references_vehicles_reg_no') " +
            "WHERE (SELECT " + REGISTRATION_NO_COLUMN + " FROM " + FUEL_USE_TABLE_NAME +
            " WHERE " + REGISTRATION_NO_COLUMN + " = OLD." + REGISTRATION_NO_COLUMN + ") IS NOT NULL; " +
            "END;");

This works fine in that it stops updates which would leave fuel_use rows referencing non-existent vehicle rows. However, I have just discovered a problem - when I try to update a differnt column in vehicle - initial_mileage - this trigger disallows it and forces a rollback. I understand why this is happening but don't know how to rewrite the trigger so only updates on the registration_number column force a rollback. I have tried:

BEFORE UPDATE ON " + VEHICLE_TABLE_NAME + "." + REGISTRATION_NO_COLUMN

but this did not work. In fact an exception was thrown.

Any help would be gratefully received.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冷︶言冷语的世界 2024-11-16 20:50:36

尝试：

db.execSQL("CREATE TRIGGER fk_update_vehicle_trigger_fuel_references_vehicles_reg_no " +
        "BEFORE UPDATE OF " + REGISTRATION_NO_COLUMN + " ON " + VEHICLE_TABLE_NAME +
        " FOR EACH ROW BEGIN " +
        "SELECT RAISE(ROLLBACK, 'update on table " + VEHICLE_TABLE_NAME + " violates foreign key constraint fk_update_vehicle_trigger_fuel_references_vehicles_reg_no') " +
        "WHERE (SELECT " + REGISTRATION_NO_COLUMN + " FROM " + FUEL_USE_TABLE_NAME +
        " WHERE " + REGISTRATION_NO_COLUMN + " = OLD." + REGISTRATION_NO_COLUMN + ") IS NOT NULL; " +
        "END;");

看看是否有效。

Try:

db.execSQL("CREATE TRIGGER fk_update_vehicle_trigger_fuel_references_vehicles_reg_no " +
        "BEFORE UPDATE OF " + REGISTRATION_NO_COLUMN + " ON " + VEHICLE_TABLE_NAME +
        " FOR EACH ROW BEGIN " +
        "SELECT RAISE(ROLLBACK, 'update on table " + VEHICLE_TABLE_NAME + " violates foreign key constraint fk_update_vehicle_trigger_fuel_references_vehicles_reg_no') " +
        "WHERE (SELECT " + REGISTRATION_NO_COLUMN + " FROM " + FUEL_USE_TABLE_NAME +
        " WHERE " + REGISTRATION_NO_COLUMN + " = OLD." + REGISTRATION_NO_COLUMN + ") IS NOT NULL; " +
        "END;");

See if that works.

回复收藏 0 原文

~没有更多了~