0

I'm creating a simple Master-Detail relationship with ClientDataSets on Delphi XE3 + SqlServer. I have configured Master-Detail through DatasetField at the client application and with the property MasterSource in the Detail TUniQuery at the server application. I'm using one DataSetProvider and one DataSource.

Server Application

**Master table**
object qyREMISION_COMPRA: TUniQuery        
Connection = DMBase.BD
SQL.Strings = (SELECT R.ID_REMISION_COMPRA, R.FECHA, R.FACTURA
               FROM REMISION_COMPRA R    
               WHERE R.ID_REMISION_COMPRA =:ID_REMISION_COMPRA)

object qyREMISION_COMPRAID_REMISION_COMPRA: TIntegerField
  AutoGenerateValue = arAutoInc
  FieldName = ID_REMISION_COMPRA
end

**Detail table**
object qyREMISION_COMPRA_PRODUCTO: TUniQuery     
Connection = DMBase.BD
SQL.Strings = (SELECT RP.ID_REMISION_COMPRA_PRODUCTO, RP.ID_REMISION_COMPRA, RP.ID_PRODUCTO
               FROM REMISION_COMPRA_PRODUCTO RP
               WHERE RP.ID_REMISION_COMPRA=:ID_REMISION_COMPRA
               ORDER BY RP.ID_REMISION_COMPRA_PRODUCTO)
SQLUpdate.Strings = (UPDATE REMISION_COMPRA_PRODUCTO
                     SET ID_REMISION_COMPRA = :ID_REMISION_COMPRA, ID_PRODUCTO = :ID_PRODUCTO
                     WHERE ID_REMISION_COMPRA_PRODUCTO = :Old_ID_REMISION_COMPRA_PRODUCTO)
MasterSource = datasetREMISION_COMPRA
MasterFields = ID_REMISION_COMPRA
DetailFields = ID_REMISION_COMPRA

**DataSetProvider**
object dspREMISION_COMPRA: TDataSetProvider
DataSet = qyREMISION_COMPRA
Options = [poCascadeDeletes, poCascadeUpdates, poPropogateChanges, poUseQuoteChar] end

Client Application

**Master ClientDataSet**
object cdsREMISION_COMPRA: TClientDataSet
ProviderName = 'dspREMISION_COMPRA'
RemoteServer = dmProvs.dspCompra
object cdsREMISION_COMPRAqyREMISION_COMPRA_PRODUCTO: TDataSetField
  FieldName = 'qyREMISION_COMPRA_PRODUCTO'
end 

**Detail ClientDataSet**
object cdsREMISION_COMPRA_PRODUCTO: TClientDataSet
DataSetField = cdsREMISION_COMPRAqyREMISION_COMPRA_PRODUCTO

To save the changes to the database, I only do for the master clientdataset cdsREMISION_COMPRA.ApplyUpdates(0)

When I do an insert works perfectly, but when I do an update I have problems with triggers in the database because the aplication execute the detail first and then the update of the master table. This is normal? I'm doing something wrong?

Frazz
  • 2,995
  • 2
  • 19
  • 33
M. Adriz
  • 1
  • 1
  • what clientdataset do you call ApplyUpdates on first ? the detail perhaps ? – GuidoG Aug 28 '17 at 15:41
  • Are you saying that you use a **single** dataset provider to retrieve the master **and** the detail dataset data, or do you use one provider for the master and another provider for the detail? – MartynA Aug 28 '17 at 16:42
  • I only call ApplyUpdate for the master clientdataset – M. Adriz Aug 28 '17 at 17:46
  • I was edit my question to include more information – M. Adriz Aug 28 '17 at 18:15
  • I have added the UniDAC tag. I think UniDAC here is much more relevant than most other tags and factors. I have been testing FireDAC vs UniDAC and I've also noticed that UniDAC always applies updates on the master before the detail. This is a problem with deletes (if you have foreign keys defined on the database). For updates it depends... are you actually changing the fields that are part of the relationship key? That is asking a bit for trouble. Anyway... you should try to test the server in isolation from the client, to be sure this is a client dataset issue or a UniDAC issue. – Frazz Aug 29 '17 at 08:09
  • You are right @Frazz.. it's a UniDAC issue. I have tested the server as you said and works the same, first the detail before the master.. I'm changing fields in the master that affects the detail trigger. Thanks a lot, I'll try with FireDAC. – M. Adriz Aug 30 '17 at 15:16
  • FireDAC handles this in the correct order, depending on the row update it has to apply. As an aside, have a look at surrogate keys (id or guid)... using those, where possible, should remove the need to do cascade updates (as the user will never modify them). Unfortunately though... cascade deletes are still executed in the wrong order by UniDAC's cached updates. – Frazz Aug 30 '17 at 15:59

0 Answers0