SQLite3 can't DELETE row, no such column Error

Question

I have a simple sqlite database setup with a table called sightings and a column within that table called name. I can run normal SELECT queries and INSERT's and UPDATE's just fine, but when I try to run DELETE, it prepends "new." into the column that I gave it and then says it doesn't exist:

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

The column is called "name" and when I select that column, it adds "new." in front of it, but when I select a wrong column that doesn't exist ("names" like above), it just says it doesn't exist normally, without the "new.". Any help with this would be greatly appreciated.

Answer 1

It appears that you have a DELETE TRIGGER and that TRIGGER wrongly uses new.name to reference the name column of the deleted row (for a DELETE TRIGGER old. is used to reference columns from the deleted row).

You should change the TRIGGER to use old.name instead of new.name.

• new.column is only applicable for INSERT or UPDATE triggers.

• old.column is only applicable for DELETE or UPDATE triggers.

As per :-

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:

• INSERT NEW references are valid
• UPDATE NEW and OLD references are valid
• DELETE OLD references are valid

SQL As Understood By SQLite - CREATE TRIGGER

Perhaps consider the following example :-

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 */

The results being :-

And the error due to the incorrect trigger :-

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