我可以在sqlite中插入触发器之前更新New吗？

For example:

例如：

create table test (id numeric, t date not null);

create trigger test_in
before insert on test
for each row
when New.t is null
begin
 -- set New.t = now();
end;

Set New.t didn't work, where can be only select/insert/update/delete stmt. I can not change the database structure (can set default value). After insert trigger also not suitable because of "not null" constrain. The only solution I've found:

设置New.t不起作用，其中只能选择/插入/更新/删除stmt。我无法更改数据库结构（可以设置默认值）。插入触发器后也因为“非空”约束而不适合。我找到的唯一解决方案：

insert into test values (New.id, now());
select raise(ignore);

test database for illustrative purposes only, in practice there are more complicated cases with calculated data. There may be something like this "update New set New.t = now()", or not?

测试数据库仅用于说明目的，实际上存在计算数据的更复杂情况。可能有类似“更新New set New.t = now（）”之类的东西？

1 个解决方案

#1

No, you can't update NEW.

不，你不能更新NEW。

What I tend to do is use a VIEW with an INSTEAD OF trigger as mu is to short commented.

我倾向于使用具有INSTEAD OF触发器的VIEW，因为mu是短评论。

In your case the best solution may be to use an AFTER INSERT/UPDATE trigger WHEN NEW.t IS NULL to update t in the affected row(s):

在您的情况下，最好的解决方案可能是使用AFTER INSERT / UPDATE触发器，当NEW.t为NULL时，在受影响的行中更新t：

CREATE TRIGGER test_in
AFTER INSERT ON test
FOR EACH ROW
WHEN (NEW.t IS NULL)
BEGIN
   UPDATE test SET t = now() WHERE id = NEW.id;
END;

FYI, your id column should probably be declared as INTEGER PRIMARY KEY...

仅供参考，你的id列可能应该被声明为INTEGER PRIMARY KEY ...

#1