Reliable way to update fields in the join table

Question

There is a Details table with a bunch of fields like T_REZKA_NV, T_REZKA_CEQ, T_REZKA_PRIM, etc. In order not to produce unnecessary entities, it was decided to make a separate OperDetails table and place several additional operations into it. Alas, customers require that all this disgrace be presented in a single table, available for editing. Accordingly, several options were tested, and results were obtained of varying degrees of despondency.

Select with joins
```
SELECT details.*, OperDetails.T_NV T_REZKA2_NV FROM Details LEFT JOIN OperDetails ON Details.NREC = OperDetails.CDetails 
```
in theory, this is the simplest option, but ADOQuery gives an error when trying to make changes to the field of the join table (searches for the T_NV field in Details) and often does not find the record that the cursor is on.
Lookup fields without joins. In this case, the event of inserting a new record is lost, the data in the table is not immediately updated, and in general, somehow everything works very crookedly.
Calculated fields. Here it is impossible to change the value of the field through the grid. Nuff said.

I also tried fields like InternalCalc, but in ADO they turned out to be identical to regular data fields. I remember somewhere I saw how you can generate insertion sentences and modifications dynamically from a select, but it turned out to be powerless to find again. Here you can see the code in more detail .

Please explain what can be done in this case, except to spit, and shove everything into Details.

ApInvent ApInvent 3,467 one 12 25 · Accepted Answer · 2015-11-03T07:13:52

From the database side, you can make a view for your query, which will put this query as a single table. And for this view instead of a trigger , so that it supports DML.

Example:

 --drop table Details; --drop table OperDetails; --1. Создадим тестовые таблицы и заполним их данными create table Details ( nrec int, value varchar2(10) ) / create table OperDetails ( CDetails int, T_NV varchar2(10) ) / insert into Details select 1, 'first' from dual union all select 2, 'second' from dual union all select 3, 'thrid' from dual / insert into OperDetails select 1, 'T_NV 1' from dual union all select 2, 'T_NV 2' from dual / --2. Создадим вьюшку над нашими таблицами create or replace view DetailOperDetails as select d.nrec, d.value, od.t_nv t_rezka2_nv from Details d left join OperDetails od on d.NREC = od.CDetails / --3. Создадим instead of trigger create or replace trigger DetailOperDetails_trg instead of insert or update or delete on DetailOperDetails for each row begin case --Для вставки данных when INSERTING then insert into Details values(:new.nrec, :new.value); insert into OperDetails values (:new.nrec, :new.t_rezka2_nv); --Для обновления данных when UPDATING then update Details set value = :new.value where nrec = :new.nrec; --Делаем merge, т.к. данных в OperDetails вообще может не быть merge into OperDetails od using ( select :new.nrec cdetails, :new.t_rezka2_nv t_nv from dual ) s on (s.cdetails = od.cdetails) when not matched then insert values (s.cdetails, s.t_nv) when matched then update set od.t_nv = s.t_nv; -- Для удаления when DELETING then delete from Details where nrec = :old.nrec; delete from OperDetails where CDetails = :old.nrec; end case; end; / --4. Пробуем DML над вьюшкой update DetailOperDetails set t_rezka2_nv = 'T_NV_3' where nrec = 3; insert into DetailOperDetails values (4, 'fourth', 'T_NV 4'); delete from DetailOperDetails where nrec = 1; --5. Смотрим результат select * from DetailOperDetails /

Not bad, but we have the 9th oracle, instead of will not work :(. I would like to still the decision on delphi.

Reliable way to update fields in the join table

1 answer 1

More articles: