sqlite - SQLite3 不能删除行，没有这样的列错误

Question

我有一个简单的 sqlite 数据库设置，其中包含一个名为Sightings 的表和该表中的一个名为 name 的列。我可以运行正常的 SELECT 查询和 INSERT 和 UPDATE 就好了，但是当我尝试运行 DELETE 时，它会在前面加上“new”。进入我给它的列，然后说它不存在：

sqlite> DELETE FROM sightings WHERE name = "blah";
Error: no such column: new.name
sqlite> DELETE FROM sightings WHERE names = "blah";
Error: no such column: names

该列称为“名称”，当我选择该列时，它会添加“新”。在它前面，但是当我选择了一个不存在的错误列（上面的“名称”）时，它只是说它不正常存在，没有“新”。对此的任何帮助将不胜感激。

标签： sqlitesql-deletenew-operatordelete-row

Answer 1

看来您有一个 DELETE TRIGGER 并且 TRIGGER 错误地使用new.name来引用已删除行的 name 列（对于 DELETE TRIGGER old. 用于引用已删除行中的列）。

您应该将 TRIGGER 更改为使用old.name而不是new.name。

• new.column仅适用于 INSERT 或 UPDATE 触发器。

• old.column仅适用于 DELETE 或 UPDATE 触发器。

按照 ：-

WHEN 子句和触发器操作都可以使用“NEW.column-name”和“OLD.column-name”形式的引用访问正在插入、删除或更新的行的元素，其中 column-name 是一个名称与触发器关联的表中的列。OLD 和 NEW 引用只能用于与它们相关的事件的触发器中，如下所示：

• INSERT NEW 引用有效
• UPDATE NEW 和 OLD 引用有效
• DELETE OLD 引用有效

SQLite 理解的 SQL - CREATE TRIGGER

也许考虑以下示例：-

CREATE TABLE IF NOT EXISTS sightings (name TEXT);
CREATE TABLE IF NOT EXISTS deleted_sightings (name TEXT); /* Table to be populated by the trigger */
INSERT INTO sightings VALUES ('blah'),('notblah'),('anothernotblah');
/* Normal deletion without triggers */
DELETE FROM sightings WHERE name = 'blah';
SELECT * FROM sightings; /* RESULT 1 (sightings table after deletion)*/

/* Add the row that was deleted again */
INSERT INTO sightings VALUES('blah');

/* Add a valid AFTER DELETE TRIGGER referring to the old column */
/* The Trigger will add a row to the deleted_sightings table using the value from the deleted row */
CREATE TRIGGER IF NOT EXISTS correct_trigger 
    AFTER DELETE ON sightings 
    BEGIN INSERT INTO deleted_sightings 
        VALUES(old.name); 
    END;
DELETE FROM sightings WHERE name = 'blah';
SELECT * FROM sightings; /* RESULT 2 (sightings table after deletion)*/
SELECT * FROM deleted_sightings; /* RESULT 3  deleted_sightings table */

/* Add a Trigger that will try to add a row to the deleted_sightings table */
/* BUT will result in the new.name column not being found as there is no */
/* new. for a DELETE trigger only old. */
CREATE TRIGGER IF NOT EXISTS incorrect_trigger AFTER DELETE ON sightings BEGIN INSERT INTO deleted_sightings VALUES(new.name); END;
/* Show the triggers */
SELECT * FROM sqlite_master WHERE type = 'trigger'; /* RESULT 4 - The triggers as per sqlite_master */
DELETE FROM sightings WHERE name = 'blah'; /* <<<<< DELETE will fail due to incorrect trigger */

结果是：-

以及由于不正确的触发器导致的错误：-

/* RESULT 4 - The triggers as per sqlite_master */
DELETE FROM sightings WHERE name = 'blah'
> no such column: new.name
> Time: 0s