Master-detail with junction table lazarus / 16-Databases ---------------------------------------------------------------------- fpcoder · 2014-02-20 06:41 I wanted to do the relation via a junction table instead of the usual two table setup. So there are three tables and a Query for each: countries                qryCountries cities                      qryCities cities_in_countries   qryJunction plus a master DBGrid and a detail DBGrid. It all works nicely with Queries behind buttons, thats no problem. Will post an example on the wiki later when its brushed up. But for some reason it got much trickier to do the same by just keyboarding the DBGrid instead of calling code behind buttons. The SELECT to show all table content is fine, but what event to use for deleting junction details for example? And if the AfterPost event is used for commiting detail records, how to let it know its an insert and not an update? or vice versa? If its an update, no new record should be added to the junction table. Not easy to ask a plain question, hope I make sense.  The SQL statements for InsertSQL, DeleteSQL is a bit to grasp. Maybe I'm lucky and someone knows just how to work this from DBGrids. Heres the tables, and I use SQLite: countries table country_id (pk,integer, autoincr) country_name (varchar(30)) cities table city_id (pk,integer, autoincr) city_name (varchar(30)) cities_in_countries table pk_id (pk,integer, autoincr) city_id (integer) country_id (integer) ---------------------------------------------------------------------- mangakissa · 2014-02-20 11:26 I'll try to understand this with my very bad English. Is this your select query Code: [Select]SELECT cc.pk_id, cc.city_id, cc.country_id, c1.country_name, c2.city_name   FROM cities_in_countries cc   JOIN countries c1 ON cc.country_id = c1.country_id   JOIN cities c2 ON cc.city_id = c2.city_idYour query is readonly. the databasecomponent (SQLdb,ZEOS) doesn't know how to handle the query back. So the returning queries would like: insertSQL Code: [Select]INSERT INTO cities_in_countries (city_id, country_id) VALUES (:city_id, :country_id) updateSQL Code: [Select]UPDATE cities_in_countries SET city_id = :city_id, country_id = :country_id WHERE pk_id = :pk_id deleteSQL Code: [Select]DELETE FROM cities_in_countries WHERE pk_id = :pk_idDon't forget your primary key when inserting records! ---------------------------------------------------------------------- fpcoder · 2014-02-21 06:35 Thanks for the examples! Whats still missing for me is what event to execute the insertion and deletion of junction records from. qryCities.InsertSQL, UpdateSQL and DeleteSQL get executed in the AfterPost event by calling ApplyUpdate there. This happens automatically every time the DBGrid is modified. But if I understand right, the qryJunction.Insert and qryJunction.Delete must be called directly by me. And I dont know what event to use for that. This is what I got: The values for aCityID and aCountryID are taken from the current record of qryCities and qryCountries. Then I guess something like this has to be executed, but from where? Insert junction: qryJunction.Insert qryJunction.ParamByName(city_id).AsInteger:=aCityID qryJunction.ParamByName(country_id).AsInteger:=aCountryID qryJunction.Post qryJunction.ApplyUpdates Transaction.CommitRetaining Delete junction: qryJunction.ParamByName(city_id).AsInteger:=aCityID qryJunction.ParamByName(country_id).AsInteger:=aCountryID qryJunction.Delete qryJunction.ApplyUpdates Transaction.CommitRetaining All primary keys are autoincrement and not updated manually ---------------------------------------------------------------------- mangakissa · 2014-02-21 07:17 Internally after post a flag is set in TSQLQuery that tis record will be inserted/updated/deleted. Calling applyupdates() TSQLQuery tells witch records must be set the modifications. And internally the InsertSQL, UpdateSQL and DeleteSQL are used to change the main table. You only have to filled in these Queries (as my example above) . As you noticed all fields of the DBGrid are readonly if you don't use InsertSQL, UpdateSQL and DeleteSQL. ---------------------------------------------------------------------- fpcoder · 2014-02-23 20:17 Updating the junction table is still somewhat a mystery.. Calling ApplyUpdates invokes the right SQL statements for the details dataset, but not the junction table. Remember there are three tables in this setup. As far as I understand, the insert of a junction record (qryJunction) can not be automatic, but must be called manually in an event of the qryCities query, as a response to an inserted city. After inserting a new row in the DBGrid and pressing enter, we always arrive in the AfterPost event. If it was possible to test for insert/update state there, everything would work now. I would just insert the junction record. But since Post puts the DataSet to dsBrowse state, there is no way of knowing if its an insert being posted or if its just an update. If its an insert then the qryJunction should be inserted a record into, posted and committed. If its an update, nothing should be done. Both editing and inserting into the dbgrid calls the AfterPost, but if to use that event for this there must be a way of checking the state? Or simply another way of inserting into the junction table? I could perhaps have a boolean variable like InsertCity that becomes true in the AfterInsert event, and false after committing and cancelling. But is that a good practice? Didnt expect it could be so difficult to put two integer values into a table.  Quote from: mangakissa on February 21, 2014, 08:17:52 amInternally after post a flag is set in TSQLQuery that tis record will be inserted/updated/deleted. Calling applyupdates() TSQLQuery tells witch records must be set the modifications. And internally the InsertSQL, UpdateSQL and DeleteSQL are used to change the main table. You only have to filled in these Queries (as my example above) . As you noticed all fields of the DBGrid are readonly if you don't use InsertSQL, UpdateSQL and DeleteSQL. ---------------------------------------------------------------------- mangakissa · 2014-02-24 10:36 I think you have a structural problem in code. Maybe you have to post some code as attachment. ---------------------------------------------------------------------- fpcoder · 2014-03-02 16:20 Using Dataset.UpdateStatus in the AfterPost event makes it possible to decide if to call Insert or Update of a record. Made an addition to the wiki, hopefully its all right. http://wiki.lazarus.freepascal.org/Databases#Dataset_UpdateStatus Will upload the example app after some more testing. Maybe something's backwards, we'll see.