Sorry, stupit mistak, I have to use NEW and not OLD in case of an INSERT
trigger!
Thanks all it works like a breeze!
----- Original Message -----
From: "Lloyd Dupont" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, June 09, 2005 12:26 AM
Subject: Re: [sqlite] SQL question
Thanks for that!
last_insert_rowid() function:
anyway, I hadn't tested the code. I mean the CREATE TRIGGER succeed.
But I didn't check if the trigger itself works well.
Now I did and have a problem...
It don't work!
I get: "SQLite Error 1 - no such column: OLD.ID"
this is my setting:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEGER PRIMARY KEY,
price double,
calories double
);
CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
INSERT INTO Properties (price) VALUES (NULL);
UPDATE Ingredients
SET property_ID = last_insert_rowid()
WHERE ID=OLD.ID;
END;
--
it looks like OLD is now the newly inserted line in Properties and not the
on in Ingredients ?!?!
is it a bug or a feature?
how to work around it?