On 29 Feb 2012, at 11:06am, hsymington <i...@hamishsymington.com> wrote:
> Simon Slavin-3 wrote:
>
>>> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem
>>> BEGIN
>>> SET NEW.SaleItem_Description='Fish';
>>> END;
>>
>> Yes. You can look at values using 'new.' but you cannot change them.
>> However, you do not need to. To perform such an operation as you list
>> above simply define a default value for the SaleItem_Description column.
>
> Thanks, Simon.
>
> If I wanted to do
> CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem
> BEGIN
> UPDATE SaleItem SET SaleItem_Description='Fish' WHERE SaleItem_Type='1' AND
> SaleItem_ID=New.SaleItem_ID;
>
> UPDATE SaleItem SET SaleItem_Description='Chips' WHERE SaleItem_Type='2' AND
> SaleItem_ID=New.SaleItem_ID;
>
> END;
>
> ...then I couldn't use default values, because you can't make defaults
> conditional.
You could do that by using conditionals in the 'DEFAULT' definition. So for
instance you can do
CREATE TABLE SaleItem (...
SaleItem_Description DEFAULT (CASE WHEN SaleItem_Type='1' THEN 'Fish'
WHEN SaleItem_Type='2' THEN 'Chips'
ELSE 'Fried onion rings' END))
But see below: you probably shouldn't be doing this at all.
> (Background: I've got a database schema in the form of a text file, which
> some software reads and converts to a SQLite database. I also need php to be
> able to read that text file and convert it into a MySQL database. I'm trying
> to work out how to define triggers so that I can change the schema
> information and have it work for both MySQL and SQLite... and am getting a
> headache in the process!)
Give up. Although the basics of SQL are present in all SQL engines, trying to
make anything but simple SQL work in more than one SQL engine tends to lead
only to ridiculously complicated code. I'm serious: it just doesn't work. If
all you have is simple tables and indexes you're fine. If you're trying to put
complicated database logic into your table definitions you always have to make
changes when you move from one SQL implementation to another. So it's not
worth even trying.
However, if the above is really what you're trying to do then I think you're
doing it wrong. You don't want TRIGGERs or even DEFAULTs. If you really have
some sort of correspondence between SaleItem_Type and SaleItem_Description then
it belongs in its own table. Store only the SaleItem_Type in this table. Make
another TABLE called ItemTypes with columns 'typeNumber' and 'description' and
feed it (1, 'Fish'), (2, 'Chips') etc.. Then when you want to know what text
is associated with a type, look it up in that TABLE using a JOIN or any other
method. That's the SQL way of doing it.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
