SQLite Forum

Error while executing query, no column with such name exists
Login

Error while executing query, no column with such name exists

(1) By XXX (xxxx89) on 2021-11-30 09:37:48 [link] [source]

I am having challenge with AFTER DELETE trigger. I created some tables and triggers, testing them, I discovered the triggers aren't working. Meanwhile I use triggers on MSSQL and MySQL successfully.

Example:

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);

CREATE TRIGGER after_del AFTER DELETE
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

I have populated the company table with records and tried deleting a record, but it tells me no COLUMN named ID exists.

(2) By Dan Kennedy (dan) on 2021-11-30 10:42:14 in reply to 1 [link] [source]

No "new" row for delete triggers. Maybe it should be "old.ID".

Dan.

(3) By XXX (xxxx89) on 2021-11-30 11:59:13 in reply to 2 [source]

Thanks Dan, Let me try it. SQLite is giving me a real tough time. Oops! It works!!

Thank you very much. Mr. Dan, please I have another question, I was hoping to post my question one after the other. I will drop it here too.

I created tables with primary and foreign keys using my SQLite Studio, the foreign keys are enabled already.

I noticed that when I populate my tables from my python application GUI, the foreign keys constraints are not maintained, they are violated and the records get saved successfully. When I run the same program connected to MySQL or MSSQL database tables with the foreign keys too, invalid data which do not match with data of the PK columns are rejected with a notification stating that the foreign key constraint is not met.

I noticed when I decide to populate the tables directly from SQLite studio, the foreign keys constraints are upheld and invalid data which do not exist in the PK columns are rejected.

So why does SQLite accept invalid data populated through Python program?

(4) By Larry Brasfield (larrybr) on 2021-11-30 12:10:54 in reply to 3 [link] [source]

Your decision to post that latter question separately is a good choice. We like to see threads with titles reflecting their content. That's hard to achieve when the only relation between some parts of the thread is their authorship.