Can a Query Class update multiple tables at once ?

hi Das:

As people point out, you can’t strictly update two tables at the same time with an SQL statement.

i.e you can’t do update table1,table2 set values = xx,yy, etc.

however, my experience is that there is always a ‘primary’ table that you are updating. In the example if invoice header table with invoice detail table, if you add a new invoice detail you might want to update a total on the invoice header.

so, SQL to the rescue in two ways.

option 1: make your own transaction within the table class. effectively you want to..

begin transaction
insert invoice record
update invoice header record
commit

in the past, we’ve changed the $insert (or $update/$delete) of a table class to call a method at the end that is $updateRelatedTables.

when the main table is updated, the extra updates of other tables get done automatically — but within omnis code

which is a segue to option 2 — put those extra updates into the database as triggers.

option 2: use triggers if the sql database allows it.

triggers effectively let you update multiple related tables at one time — by only updating the one table.

eg, in the example above, you would

insert invoice record.

the database would have a trigger on the invoice record that effectively says..

when invoice record is inserted, also update the header total
when invoice record is changed, updated header with delta difference
when invoice record is deleted, subtract total from invoice header

the benefit of a trigger is that the rule is absolute.
— if you change a specific field in a data record, another table will always be updated, according to the rule you wrote. it doesn’t matter if you do it in omnis, another programming language, or (in the case of postgres), through a tool like pgadmin

— it also makes the work happen at the database, so its FAST
— it makes an atomic transaction, implicitly
— since it works in the background (automatically) … it can be harder to debug, as it were.

triggers are not for every one — some people like them, others dont. we have about 200 or so of them in our database for various reasons. mostly for the reasons of data integrity and performance.

hope that helps.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug@artsman.com
www.artsman.com
Phone (403) 650-1978

see you at the third annual users conference
tickets.proctors.org/TheatreManager/95/online?performance=29086 <tickets.proctors.org/TheatreManager/95/online?performance=29086>

> On Mar 13, 2018, at 7:22 PM, Das Goravani <das@goravani.com> wrote:
>
>
> Can you update the fields and tables present in a query class by doing an $update on a row/list defined from the query class?
>
> Ie
>
> Mylist.$update(loldrow)
>
> Where mylist is defined from a query class containing say 3 tables, fields from 3 tables
>
> Will all the present columns get written back to the data?
>
> So can you update from a multi table query to multi tables?
>
> I know you can read from multi tables, that’s obvious. I didn’t see updating covered in the docs for query classes.
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com