| Subject | Re: [IBO] Getting the value of a generator |
|---|---|
| Author | Helen Borrie |
| Post date | 2012-04-13T20:30:42Z |
At 12:19 AM 14/04/2012, you wrote:
Create the dependencies between the master and detail sets using the appropriate linkages that IBO provides. Use the GeneratorLinks property to tell IBO which field in the master set needs that value. IBO will fetch the value automatically when you insert the master record and, from there on, IBO will take care of writing that value to detail records as they are inserted into the client sets.
If the user decides not to commit then IBO has the smarts to cancel the master record. Of course, a generated value never "rolls back" but the only cost is that you have a value that is never used. If your business rules say that this number must never have a gap in the sequence, then your design is wrong. If you must have a sequence with no gaps, as e.g., for auditing invoice numbers or accounting for serial numbers then don't use those data pieces as keys. Keys should have no semantic loading.
Also take care that the triggers you use to effect the relationship at the server side take into account the fact that applications are fetching the key value before the inserts hit the database. Specifically, test for NULL before letting the trigger assign a value to a key.
...
if (new.key_value is null) then
new.key_value = gen_id (generator_name, 1);
...
Helen
>Helen, as always - Thanks for your timely post.Ouch! Don't do it!
>
>As you can probably tell, I am using the primary key in my master table to identify records in a detail table. I am getting the anticipated value of the new master record before it is committed in case the user decides not to commit. If the user commits, it all goes great unless another user happens to sneak in. This will not likely be the case in this database (famous last words).
Create the dependencies between the master and detail sets using the appropriate linkages that IBO provides. Use the GeneratorLinks property to tell IBO which field in the master set needs that value. IBO will fetch the value automatically when you insert the master record and, from there on, IBO will take care of writing that value to detail records as they are inserted into the client sets.
If the user decides not to commit then IBO has the smarts to cancel the master record. Of course, a generated value never "rolls back" but the only cost is that you have a value that is never used. If your business rules say that this number must never have a gap in the sequence, then your design is wrong. If you must have a sequence with no gaps, as e.g., for auditing invoice numbers or accounting for serial numbers then don't use those data pieces as keys. Keys should have no semantic loading.
> I would, however, prefer to do it the right way, but not sure how to approach it. Any suggestions on avoiding mismatches?Absolutely. Just DON'T ever try to use client-side code to implement relationships that try to out-guess the metadata. Tell IBO about the metadata dependencies and just let it get on with what it is designed to do.
Also take care that the triggers you use to effect the relationship at the server side take into account the fact that applications are fetching the key value before the inserts hit the database. Specifically, test for NULL before letting the trigger assign a value to a key.
...
if (new.key_value is null) then
new.key_value = gen_id (generator_name, 1);
...
Helen