I’m starting new Xojo DB based project i need to get done ASAP and am planning to use PostgresSQL and/or SQLite.
I was never an SQL expert, and what SQL I do know is rusty.
I have mostly used SQLite for pretty simple stuff and never used Posgress outside of fooling around with it a little 10+ years ago. (so will likely have more question about it later)
I could easily do what I need to in Xojo code (which is how I handled things in the past), but I know it really should be in the DB
So I was wondering if anyone can help me with how to do this in Postgres and Sqlite
I would have a Parent table:
Type: Single Character
LotCount: Int64 - Count of lots created for THIS material. Used to make the lot ID
And child table:
MaterialKey: int64 - Key0 of material table
LotNo: String - Formed from MaterialTable.Type and MaterialTable.LotCount
When I insert a record in the Lot table for a material:
- I need MaterialTable.LastLotCount to be incremented
- LotTable.LotNo to be formed from:
MaterialTable.Type + 4 Digit Hex Number of MaterialTable.LastLotCount
In Sqlite I assume this could be done by an insert trigger, but I am unclear on the details.
I am also sure it could be done in Postgres with an Insert trigger too but have no idea of the details there at all.
I would really appreciate some example SQL for each DB to get me going!
Also is there a good (easy to use for a non-expert) free Mac GUI Postgres DB creation app?