只有在没有提供PK的情况下，使用触发器才能防止在SQLite中更改列

Question

我试图阻止任何用户或程序更改我的SQLite数据库中的特定列。我想做的只是为更新语句创建一个触发器，在这里我可以保留数据集的旧值。

一个小的表和触发器示例如下:

create table testtable
(
    id INTEGER   PRIMARY KEY AUTOINCREMENT,   -- column shouldn't be unchabgeable
    col1  INTEGER   default 0   not null,
    col2  TEXT(10)  default ' ' not null,        -- column shouldn't be unchangeable
    col3  TEXT(30)  default ' ' not null         -- column shouldn't be unchangeable
);
create trigger testtable_trigger
after update on testtable for each row
begin
    update testtable
        set id    = old.id,                      -- set the old values
            col2  = old.col2,
            col3  = old.col3
        where id = old.id;
end;

我在这里使用after update触发器，因为before update根本不起作用。

示例数据集

insert into testtable (col1, col2, col3) values(42, "a", "b")

每当我现在尝试修改col3与更新语句这工作。col2保持它的初始(默认)值。对于要修改col2和col3的update语句也是如此-它们保持它们的值。如果col1, col2和col3只给出col1实际上改变了，就像我想要的那样。

update testtable set col3 = 'changed' where id = 1;                                  -- nothing changed
update testtable set col2 = 'changed', col3 = 'changed' where id = 1;                -- nothing changed
update testtable set col1 = 1234, col2 = 'changed', col3 = 'changed' where id = 1;   -- col1 changed

我的问题是现在。对于任何更新语句，给予PK id所有数据更改。无论如何。在这种情况下，似乎没有执行触发器。还是我误解了什么?

update testtable set id = 23, col2 = 'changed', col3 = 'changed' where id = 1;    -- everything changed

Answer 1

当ID从1更改为23时，必须使用如下的UPDATE语句来恢复它:

UPDATE testtable
SET id = 1
WHERE id = 23;

也就是说，WHERE子句中要比较的ID值必须是new.id。

(当启用递归触发器时，此将爆炸)

---

要真正阻止更改，而不是静默地恢复它，从触发器引发一个错误:

CREATE TRIGGER testtable_trigger
BEFORE UPDATE OF id, col2, col3 ON testtable
BEGIN
    SELECT RAISE(FAIL, "these columns are read only");
END;