SQL question

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL question

Lloyd Dupont
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Lloyd Dupont
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" <[hidden email]>
To: <[hidden email]>
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Martin Engelschalk
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" <[hidden email]>
> To: <[hidden email]>
> 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Lloyd Dupont
thanks Martin it worked!
although I replaced your (SELECT MAX(ID) FROM Properties)  by ROWID.
is it sound?

like that:
CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
    INSERT INTO Properties (price) VALUES (NULL);
    UPDATE Ingredients
        SET property_ID = ROWID
        WHERE ID=OLD.ID;
END;


-- price doesn't matter. I set it up much later.. (whenever the user want,
in fact), but I want my property line created and correctly linked!

Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Martin Engelschalk
Hi Lloyd,

i am not sure, but i checked the documentation and i don't think it ist
sound.
In your Update, the ROWID semms to refer to ingredients.rowid. However,
you want to set properties.rowid.
Can it be that it works, because Properties and Ingredients happen to
have the same number of rows?

Martin

Lloyd Dupont schrieb:

> thanks Martin it worked!
> although I replaced your (SELECT MAX(ID) FROM Properties)  by ROWID.
> is it sound?
>
> like that:
> CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
> BEGIN
>    INSERT INTO Properties (price) VALUES (NULL);
>    UPDATE Ingredients
>        SET property_ID = ROWID
>        WHERE ID=OLD.ID;
> END;
>
>
> -- price doesn't matter. I set it up much later.. (whenever the user
> want, in fact), but I want my property line created and correctly linked!

Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Derrell Lipman
Martin Engelschalk <[hidden email]> writes:

> Hi Lloyd,
>
> i am not sure, but i checked the documentation and i don't think it ist
> sound.
> In your Update, the ROWID semms to refer to ingredients.rowid. However,
> you want to set properties.rowid.
> Can it be that it works, because Properties and Ingredients happen to
> have the same number of rows?
>
> Martin
>
> Lloyd Dupont schrieb:
>
>> thanks Martin it worked!
>> although I replaced your (SELECT MAX(ID) FROM Properties)  by ROWID.
>> is it sound?
>>
>> like that:
>> CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
>> BEGIN
>>    INSERT INTO Properties (price) VALUES (NULL);
>>    UPDATE Ingredients
>>        SET property_ID = ROWID
>>        WHERE ID=OLD.ID;
>> END;

According to the documentation, you should be able to use the
last_insert_rowid() function:

    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;

Derrell
Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Lloyd Dupont
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?

Reply | Threaded
Open this post in threaded view
|

Re: SQL question

Lloyd Dupont
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" <[hidden email]>
To: <[hidden email]>
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?
>