sqlite trigger causes “no such column” exception

问题:

I'm a newbie with sql triggers and am getting an ESQLiteException on what seems like a simple example. When I try to modify the "memberTag" column in an existing row, I get the exception "no such column: memberTag". If I drop the trigger, the exception goes away and the row gets updated.

I'm using SQLite and I'm using the "SQLite Expert Personal" app to do this experimenting.

I have this table:

CREATE TABLE [znode] (
  [description] CHAR NOT NULL, 
  [memberTag] CHAR);

and this trigger:

CREATE TRIGGER [memberTagTrigger]
AFTER UPDATE
ON [znode]
FOR EACH ROW
WHEN length(memberTag)=0
BEGIN
update znode
  set memberTag = null;
END;

My update experiment data is something like this:

description  memberTag
one          x
two          (null)

And when I try to change (null) to "y" using SQLite Expert Personal, it throws the exception.

回答1:

The problem is in the WHEN clause: the database does not know where memberTag comes from, because there are two possible rows, the old one, and the new one.

Use either OLD.memberTag or NEW.memberTag.

(There is another problem: the UPDATE will change all rows in the table, because you forgot the WHERE clause.)