您似乎有一个DELETE TRIGGER,并且TRIGGER错误地使用 new.name 来引用已删除行的名称列(对于旧的DELETE TRIGGER来说,是用来引用已删除行中的列行)。
您应将触发器更改为使用 old.name ,而不是 new.name 。
按照:-
WHEN子句和触发操作都可以访问以下元素:
使用参考引用插入,删除或更新的行
格式为“ NEW.column-name”和“ OLD.column-name”,其中column-name为
触发器与之关联的表中列的名称。
新旧引用只能用于以下事件的触发器中:
它们相关,如下所示:
- 插入新引用有效
- 更新NEW和OLD参考是
有效
- 删除旧引用有效
SQL As Understood By SQLite - 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
本文链接:https://www.f2er.com/2937507.html