Simon Slavin-3 wrote:
>
>> 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.
>
>
Thanks Simon; this does sound like a less headache-driven way of doing it. I
was simplifying things slightly for an example; the actual situation is more
complicated. What I'm trying to do is something like the below.
Prerequisites:
A table called 'Sale', which has a column for TaxInclusive, and which has a
column for a UUID
A table called 'SaleItem', which has a column for Sale_UUID (i.e. the sale
it's linked to), a column for TaxRate, and a column for LinePrice.
If SaleItem_TaxRate<>0 then
If (SELECT Sale.Sale_TaxInclusive FROM Sale, SaleItem WHERE
Sale.Sale_UUID=SaleItem.SaleItem_SaleUUID) = 1 then
UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice
WHERE SaleItem_ID=New.SaleItem_ID;
UPDATE SaleItem SET SaleItem_TaxAmount =
SaleItem_LinePrice-(NEW.SaleItem_LinePrice/((NEW.SaleItem_TaxRate +
100)/100)) WHERE SaleItem_ID=New.SaleItem_ID;
Else
UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice +
(SaleItem_LinePrice*SaleItem_TaxRate/100)) WHERE
SaleItem_ID=New.SaleItem_ID;
UPDATE SaleItem SET SaleItem_TaxAmount =
(SaleItem_LinePrice*SaleItem_TaxRate/100)) WHERE
SaleItem_ID=New.SaleItem_ID;
End
Else
UPDATE SaleItem SET SaleItem_GrossAmount = SaleItem_LinePrice
WHERE SaleItem_ID=New.SaleItem_ID;
UPDATE SaleItem SET SaleItem_TaxAmount = 0 WHERE
SaleItem_ID=New.SaleItem_ID;
End
If you think there's a way of doing that sort of thing - plus further nested
Ifs - in tables, rather than with triggers, then I'd bite your hand off to
hear it, because I can't see how to do it. I don't particularly want to do
it in the application logic if I can help it, because if I'm trying to
replicate this functionality in two applications (let's say C and PHP) then
there's a chance of having two different sets of logic if an update I
perform is correct in one and incorrect in another.
Thanks,
Hamish
--
View this message in context:
http://old.nabble.com/SET-NEW.FieldName-in-trigger-tp33413040p33413634.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
