如何处理 SQLite 缺少的功能:禁用触发器?
如何处理 SQLite 缺失的功能:禁用触发器
?
我没有让它存储特定表的触发器名称。
例如,我如何删除所有触发器?
你会怎么办?
How to handle a missing feature of SQLite: disable triggers
?
I don't have it stored the name of triggers for a specific table.
For example how can I drop all triggers?
What would you do?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
现在已经是 2015 年了,SQLite 中仍然没有“禁用触发器”。对于移动应用程序来说,这可能会出现问题 - 特别是如果它是需要离线功能和本地数据的企业应用程序。
即使您没有将每个插入包装在单个事务中,触发器执行也会减慢初始数据加载的爬行速度。
我使用 SQLite SQL 相当简单地解决了这个问题。我有一个不参与初始化加载的设置表。它保存键/值对的“列表”。我有一个名为“fireTrigger”的键,其位值为 0 或 1。我的每个触发器都有一个选择值的表达式,如果它等于 1,则触发触发器,否则不会。
该表达式是对与触发器相关的数据进行评估的任何表达式的补充。例如:
在简单的干净效果中,这允许我通过对设置表的简单
UPDATE
来禁用/启用触发器So here it is 2015 and there still is no 'disable triggers' in SQLite. For a mobile Application this can be problematic--especially if it's a corporate App requiring offline functionality and local data.
An initial data load can be slowed to crawl by trigger execution even when you don't wrap each insert in an individual transaction.
I solved this issue using SQLite SQL fairly simply. I have a settings table that doesn't participate in the init load. It holds 'list' of key/value pairs. I have one key called 'fireTrigger' with a bit value of 0 or 1. Every trigger I have has an expression that selects value and if it equals 1 it fires the trigger, otherwise it doesn't.
This expression is in addition to any expressions evaluated on the data relating to the trigger. e.g.:
In simple clean effect this allows me to disable/enable the trigger with a simple
UPDATE
to the settings table我编写了一个非常简单的扩展函数来将布尔值设置为 true 或 false。
以及检索该值的函数 (GetAllTriggersOn())。
通过这个函数,我可以定义所有触发器,例如:
I wrote a very simple extension function to set a boolean value to true or false.
And a function to retrieve this value (GetAllTriggersOn()).
With this function I can define all my triggers like:
SQLite 将架构(元)信息存储在内置的
sqlite_master
表中。要获取可用触发器的列表,请使用以下查询:
SQLite stores schema (meta) information in the built-in
sqlite_master
table.To get a list of available triggers use the below query:
扩展 Nick Dandoulakis 的答案,您可以删除所有相关触发器,然后在事务完成之前恢复它们:
Expanding on Nick Dandoulakis's answer, you could drop all relevant triggers and then reinstate them before the transaction's completion:
在数据库中设置一个标志并在触发器 WHEN 条件中使用它。
假设您想在插入后在“clients”表上创建一个触发器。您已经创建了一个带有 TINYINT“triggers_on”字段的表“trigger_settings” - 这是您的标志。然后,如果您想关闭过滤器,则可以将该字段设置为 0;如果您想重新打开过滤器,则可以将该字段设置为 1。
然后,您使用 WHEN 条件创建过滤器来检查“triggers_on”字段。
例如:
Set a flag in your database and use it in the triggers WHEN condition.
Say you want to create a trigger on the "clients" table after an insert. You have created a table "trigger_settings" with a TINYINT "triggers_on" field - this is your flag. Then you can set the field to 0 if you want to turn off the filters and to 1 when you want to turn them back on.
Then you create your filter with a WHEN condition that checks the "triggers_on" field.
For example:
也许您可以创建一个存储过程来删除和创建它们。这对你有好处吗?
Maybe you can make a stored procedures for droping and creating them. Is that good for you ?
扩展其他答案这就是我正在做的事情。考虑到这会禁用数据库中所有表的所有触发器,除了
spatialite
使用的一些触发器之外Expanding other answers this is how i'm doing it. Take into account that this is disabling all triggers for all tables in the database except some of then used by
spatialite