Hi,
i am not sure if i understand your problem correctly. Perhaps the
following does it:
CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
INSERT INTO Properties (price) VALUES (<some price>);
UPDATE Ingredients SET property_ID (select max(id) from properties)
WHERE ID=OLD.ID;
END;
Because the id is omitted in the Insert - Statement to 'properties',
sqlite supplies one, which you can then update into ingredients.
However, I'm not clear where the price is supposed to come from.
Martin
Lloyd Dupont schrieb:
BTW, one more question / precision.
in INSERT INTO Properties() I didn't pass the value for ID.
because basically I want an auto-incremented value which I don't have to
worry about.
maybe that's not the way to use such value ?!....
----- Original Message ----- From: "Lloyd Dupont" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, June 08, 2005 10:37 PM
Subject: [sqlite] SQL question
I have 2 related table:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEGER PRIMARY KEY,
price double
);
When I create a new Ingredient I would like to create a new property
for this ingredient and setup its property_ID.
because there is no stored procedure I was thinking to use a trigger
like this one:
CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
INSERT INTO Properties () VALUES ();
UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID;
END;
but it doesn't work....
I could do with some help for the SQL syntax :D