database triggers and views

[jalz]

Hi Guys,

Im really loving LC,got a simple app planned and am just walking through sample files working out how to do certain things. One of the biggest issue I think I may have is to enter data into various tables all at once. Someone kindly suggested that I look at database triggers and indeed after having read through some of the stuff might be the way to overcome this.

Has anyone got a very basic sample file I can disect and learn from, it could be a two table file where the user enters data onto a form and some goes into table 1 and the other goes into table 2 with keys attached to them so I can then relate back to them.

Any samples or help would be really appreciated, so far got my db to add, amend, delete etc all in one table but Uve yet to figure out how to use triggers and views....

Im using SQLite initially to get me started and am aware it inherits some limitations to triggers and views compared to the fullblown SQL versions out there.

Many thanks in advance all

[mwieder]

Check out the section here on triggers for some simple examples:

http://linuxgazette.net/109/chirico1.html

[jalz]

Many thanks for the link mwieder.
Ive been searching google, and experimenting with triggering code on sample databases.

Think Ive got my head around how triggers work. Unfortunately, after viewing the many samples and reading verious blogs I dont think this is what I need....

I dont know if any of you are familiar with FileMaker out there? A developer can create a form which when the user enters data in, the data gets posted to the database. Thats fairly easy to replicate in Livecode using SQL statements. However FileMaker have a portal object, and what that allows you to do is enter in data into a related table quite easily.

Im going to try and attempt this kind of behaviour in Livecode with the datagrid object. What I would like to do is create an empty form - lets say invoices and that form is going to have multiple invoice line items. When the user hits submit, I want the invoice data to go in the invoice table, and the invice line items into the line_items table, however I need to figure out how best to get the primary key data from the invoices table parsed down to the line_items table.

I was going to create a trigger to do this, but I dont think thats the right type of usage for it after reading material out there. ANyone have any recommendations on how I can try and achieve what I want to do?

Thanks
Jalz

[bangkok]

I'm sorry, but I don't really understand why you bother with "triggers" (a difficult "beast", especially when you start with SQL)...

You're talking about "portals" in FM.

It's quite easy to do the same.

Just do several SQL queries in LiveCode.

-you want to add automatically a line in table B when you insert a line in a table A ?

just do 2 queries, in the same script.


You keep the control. It's easy to read. etc.

Again, why bother ?

However, to "read" data, of course there you'll have to use complex SQL queries (with JOIN etc.). That's the way do to it if you want to emulate "portals", in reading data.

But for INSERT and UPDATE, really, keep it simple : several queries in the same LiveCode script.

[jalz]

Hi bangkok,

Thanks for replying. I agree, think Im going to keep things simple, was reading alot more on triggers - not what I want at the moment but would be great for audit logging in the future.

So if Im going to create a portal like behaviour lets say I have a customer table and a comms table.

The data structure is as follows:

customer
customerID_pk (Auto increment integer)
customerName


comms
commsID_pK (Auto increment integer)
customerID_fk
Tel_fax_email

Basic SQLite insert statements
INSERT INTO customer (customerName)
VALUES ('Jalz Inc')

INSERT INTO comms (Tel_fax_email)
VALUES ('Jalz@hotmail.com') - but how do I get the customerID_pk generated on the insert of the customer row to pass down to the insert of the comms row?

[bangkok]

INSERT INTO comms (Tel_fax_email)
VALUES ('Jalz@hotmail.com') - but how do I get the customerID_pk generated on the insert of the customer row to pass down to the insert of the comms row?

You should perhaps change your primary key : in customer, the customername should be unique, no ?

Anyway. You can keep the int autoincrement.

And you can "get it", by... reading it.

So, that would give you 3 queries

1 to insert the new customer
2 to select the new customerID created just before (by doing a "ORDER BY customerID DESC LIMIT 1" or "MAX(customerID)")
3 to insert the contact with the customerid

Or you could use the LAST_INSERT_ID() (MySQL) function.

Or last_insert_rowid() (sqllite) function

I'm sure SQL purists would indeed "cry" against such scheme. But again, sometimes it's better to do it with "brute force". My philosophy at least.

[jalz]

Thansk again,
Yes the ccustomername should be unique - thanks.

Ive had some success with the last_insert_rowid() in sqlite, so will try and attempt to do it using this.

Whats the best object in Livecode to prepare for data entry, filemaker portal like (i.e. records with lots of rows and with a slider on the right). Would it be the datagrid property.

Will probably start a new thread with sqlite syntax issues..... I can probably see how to do the inserts, but no doubt I will need to work hout how to do a loop of inserts if the 'portal' propery has more than one record entered in

Let me give it ago.

[bangkok]

Whats the best object in Livecode to prepare for data entry, filemaker portal like (i.e. records with lots of rows and with a slider on the right). Would it be the datagrid property.

Watch out. Datagrids are (really) great. But to master Datagrids, you'll need some time (i understand that you start with LC).

So again, i'll go to the easiest way :

-for data input : you should perhaps use several text fields, on the same card. It's easy, and you can add several "input checks" (in order to check the validity of what the user is typing).

-for data display, in this case, datagrids are great.