0

I have master detail

CachedUpdates for the master true
CachedUpdates for the detail true
DetailCascde for the detail true

The master deals with one record:

select * from orders where order_id=:order

First I pass -1 as dummy parameter to get an empty master record:

orders.Close;
orders.Params[0].AsInteger := -1;
orders.Open;

Than I fill the order id with -1 to build the relationship between the master and the detail:

orders.Append;
orders.Fields[0].AsInteger := -1;
orders.Post;

I insert into the detail successfully with Append and Post

The problem is in the Firebird database I have this line on before insert trigger for the master 

new.order_id = coalesce((select max(order_id) from orders) + 1, 1);

I ApplyUpdates 

Orders.ApplyUpdates(-1);
dOrder.ApplyUpdates(-1);

So when I ApplyUpdates for the master, the detail won't apply because the master id is altered by the server.

How to solve such scenario?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
S.FATEH
  • 451
  • 8
  • 16
  • 3
    You should **never** use `Select max(some column) +1)` to generate a new pk value, because it will not produce correct results in a multi-user environment. Use a server-side generator instead - that's what they are there for. – MartynA Mar 03 '19 at 09:48
  • @MartynA could you provide more information that's why i use CachedUpdates in fact i migrate DBExpress apps to FireDac to provide multiusers on lan with EventsAlerter... – S.FATEH Mar 03 '19 at 10:08
  • Sorry? Firebird generators are fully described in tthe FB documentation. – MartynA Mar 03 '19 at 11:09
  • even if i use generators the same problem because id's not showed in user interface if i use generator and make them visible in user interface in case the user cancel the operation due to cachedupdates i lost numbers... – S.FATEH Mar 03 '19 at 11:20
  • What's wrong is using a gui to enable a user to cancel out of a new record. Best practice is to gather all the information for the new record(s) and insert them **before** giving the user the opportunity to edit or delete them. So, IMO you are avoidably creating a problem for yourself - a user should not be permitted the opportunity to change what they're doing part-way through adding a master-detail. They should have to make up their mind and get it right beforehand. – MartynA Mar 03 '19 at 12:15
  • it's master detail with cached updates ! if it was like you said why using cached updated best practices not always suitable.. any way thank's for your comment's – S.FATEH Mar 03 '19 at 12:40
  • 1
    As Martyn says, don't use `max(some column) +1` to generate ids, it will break down under concurrent execution (depending on the transaction isolation either causing delays or transaction failures, or generated duplicate ids). Use a generator. As to your problem, if you don't want Firebird to override an id, then you should generate it yourself (using a sequence (aka generator)), not in a trigger. – Mark Rotteveel Mar 03 '19 at 15:28
  • 1
    Lost numbers are not an issue: https://stackoverflow.com/questions/11591228/primary-key-id-reaching-limit-of-bigint-data-type#16936665 – FredS Mar 03 '19 at 16:49

0 Answers0