Obtain Trigger generated values after ApplyUpdates with a TClientDataSet

Question

I have a (Firebird) DB. For most of my tables I have a trigger which fires before insert which will create the Primary Key (PK) for me via a generator as well as write to the newly inserted records a Created Date value and a Created By value. I also have an update trigger which writes to an Updated Date field and an Updated By field.

eg (Client is a table in my DB):

create trigger t_client_id for client
active before insert
as begin
  new.client_id = gen_id(gen_client_id, 1);
  new.created = current_timestamp;
  new.created_by = current_user;
  new.lock_vn = 1;
end ^

create trigger t_client_update for client
active before update
as begin
  new.updated = current_timestamp;
  new.updated_by = current_user;
end ^

When I apply updates thru my ClientDataSet (CDS) - which are attached to remote TDataSetProviders via a TDSProviderConnection, how can I "retrieve" these generated values? If I edit an existing one (which will in turn call the t_client_update trigger, calling RefreshRecord will get the updated and updated_by fields. However, the Doco says to use that method cautiously, so that may not be the correct way to achieve this. I call it straight after I've called ApplyUpdates(-1).

The CDS I use only contains the one record I am attempting to Edit. For a New record, the CDS is in dsInsert mode. Everything is written to the DB ok so I just need to get this new data back out again. I have also tried using a CDS which contains ALL records in the table too to see if it was any simpler but didn't make any difference - unsurprisingly. The reason I need this information is simply to show to the user in DB Aware controls these values. They are read only.

I could call a Get on the record I guess when editing an existing record, using the PK, but that won't help for an Insert as I don't know what the new PK is.

Example of where I attempt to ApplyUpdates to my CDS (actDSSave is a TDataSetPost action)

  dsState := actDSSave.DataSource.DataSet.State;
  DoApplyUpdates(-1);
  if dsState = dsEdit then
    TClientDataSet(actDSSave.DataSource.DataSet).RefreshRecord;

I am using TIBQuery for my dataset attached to the remote DataSetProvider. This query SQL is a simple select * from client where client_id = :client_id. I have tried associating this query with a TIBUpdateSQL too as well as trying to set poAutoRefresh to true in the DataSetProvider.

So is it possible to obtain these Trigger generated values this way or do I need to approach it in a different way? Another way I can think of, is to create stored procedures which do CRUD against each table and use that instead (with appropriate in/out params to return this new data) but hopefully I don't have to go down that track. Hopefully I have provided sufficient info here to explain and replicate the issue.

Thanks

EDIT Realised in above, DoApplyUpdates(-1) is my own method. It's implementation at the moment is simply:

FdatCommon.cdsClient.ApplyUpdates(MaxErrorCount);

FdatCommon is a TDataModule containing my CDS.

for inserts, maybe you'd make a separate query that both adds new row AND returns generated values ? — Arioch 'The
With Firebird 2.1 and higher, you can use the returning clause in your insert statement. — Ondrej Kelle
If i could utilise that feature using a TDatasetProvider and TClientDataSet I think I could make it work - I'd be happy to call a GET against after the edit/insert. — Jason

Marcodor Marcodor · Accepted Answer · 2012-09-29T13:47:03

You simply can't get "generated" values without new requery (RefreshRecord) of data after Post.

It's because triggers runs on server side when you call ApplyUpdates, but TClientDataSet does not refresh by default posted record. For example other libraries FIBPlus have an option to do it automatically.

About inserts, TIBDataSet have GeneratorField property. Using it, dataset query and increment generator value separatelly before insert. So you will have PK values after post even on inserts. But avoid using it again in trigger.

MIDAS (TClientDataSet) is a great library, but his general / universal architecture loose DB specific features (such as retriving values from inserts) compared to dedicated libraries for specific DBMS, such as FibPlus. By the way I saw TpFIBClientDataSet. It work in conjunction with TpFibDataSet.

Obtain Trigger generated values after ApplyUpdates with a TClientDataSet

1 Answers