英文:
SQLite trigger to increment a column value when a record is altered
问题
我正在编写一系列更新查询的过程，用于评估表格的记录，并在适当时更改一个或多个字段/列。
我需要一种跟踪已更改记录的方法。为此，我过去通常使用以下触发器来修改字段sqlmodded，然后可以将仅更改的记录导出到新表中，该表的内容然后用于更新底层文件的元数据。触发器如下：
/* 老实用的触发器 */
CREATE TRIGGER sqlmods
AFTER UPDATE
ON alib
FOR EACH ROW
WHEN old.sqlmodded IS NULL
BEGIN
UPDATE alib
SET sqlmodded = TRUE
WHERE rowid = NEW.rowid;
END;
我想进一步使用sqlmodded，通过利用一个单一的触发器来增加sqlmodded的值。当所有更改都已处理时，sqlmodded的状态将表示以下内容：
- 如果
> 0，表示记录已经经历了更改的次数 - 如果
null（或如果需要默认为0），表示记录没有被更改
经过多次尝试后，我得出了以下结论，似乎是有效的：
/* 提议的触发器，用于表示更改和已处理更改的次数 */
CREATE TRIGGER IF NOT EXISTS sqlmods
AFTER UPDATE
ON alib
FOR EACH ROW
WHEN old.sqlmodded IS NULL
BEGIN
UPDATE alib
SET sqlmodded = iif(sqlmodded IS NULL, '1', (CAST(sqlmodded AS INTEGER) + 1))
WHERE rowid = NEW.rowid;
END;
由于涉及的表包含数十万行数据和多个字段/列，其中运行许多更新查询，我想知道如果我将sqlmodded定义为INTEGER并将其默认值设置为0，是否会有显著的性能提升，或者是否有更好的方法来完成这个任务？
英文:
I am in the process of coding a series of update queries that evaluate a table's records and make changes to one or more fields/columns when appropriate.
I need a way to track which records have been changed. To this end I've historically used the following trigger to modify a field sqlmodded which then enabled me to export only changed records to a new table, the contents of which is then used to update metadata in underlying files. The trigger is as follows:
/* old faithful trigger */
CREATE TRIGGER sqlmods
AFTER UPDATE
ON alib
FOR EACH ROW
WHEN old.sqlmodded IS NULL
BEGIN
UPDATE alib
SET sqlmodded = TRUE
WHERE rowid = NEW.rowid;
END;
I'd like to go one step further and use sqlmodded for dual purposes by leveraging a single trigger that increments sqlmodded. When all changes are processed the state of sqlmodded will signify the following:
- if
> 0it denotes the number of changes to which the record has been subjected - if
null(or0if necessary to default to0) the record has not been altered
After much fiddling I've landed on the following, which does appear to work:
/* proposed trigger to signify change and number of changes processed */
CREATE TRIGGER IF NOT EXISTS sqlmods
AFTER UPDATE
ON alib
FOR EACH ROW
WHEN old.sqlmodded IS NULL
BEGIN
UPDATE alib
SET sqlmodded = iif(sqlmodded IS NULL, '1', (CAST (sqlmodded AS INTEGER) + 1) )
WHERE rowid = NEW.rowid;
END;
As the table in question contains hundreds of thousands or rows and many fields/columns against which many update queries are run I was wondering whether there would be any significant performance improvement if I defined sqlmodded as INTEGER and defaulted its value to 0 or whether there is there a better way to accomplish this?
答案1
得分: 0
现在已经利用这个触发器来处理实际上超过50万次的更改，它的工作效果符合预期，性能也令人满意，因此不需要进一步的优化意见。
英文:
Having now utilised this trigger to process what in effect amounts to over 500k changes it works as intended and its performance is satisfactory, so no further optimisation comments required.
CREATE TRIGGER IF NOT EXISTS sqlmods
AFTER UPDATE
ON alib
FOR EACH ROW
WHEN old.sqlmodded IS NULL
BEGIN
UPDATE alib
SET sqlmodded = iif(sqlmodded IS NULL, '1', (CAST (sqlmodded AS INTEGER) + 1) )
WHERE rowid = NEW.rowid;
END;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论