| Subject | Re: [firebird-support] Generator and Trigger confusion ? |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-11-11T22:43:53Z |
At 07:35 PM 11/11/2003 +0100, you wrote:
Something that should be clear to "newbies" is that, once you have got the
generator value, nobody else can get that value - PROVIDED that you do not
have SPs or user utilities that tinker with the generator. The
independence of generators is what allows you to work this way -- unlike
"identity" or "autoinc" types, you don't need to create the row in order to
get the PK value.
For M-D, fetch the generator value for the master's PK into a program
variable *before* you begin to create the master. But *also* have the "if
null then generate" trigger in the database to make the maintenance of the
generator independent of anyone's application code, manual tinkerings with
utility tools, batch inserts or whatever.
-----------------------------------------------------
I think this thread has lost sight of Lee's original question. His problem
seems to be that he is importing rows that *already* have values for the
PK, which is causing key violations against existing record keys. It's not
generators, per se, that cause this problem - it's inadequate (or lack of)
design of keys for replication purposes.
This is a design problem that will NEVER be solved by tinkering with the
generators.** If you have to read a key value or a generator value in
order to work out what the next key should be, then you have a potentially
destructive, major design flaw.
** "No design should ever tinker with generators" - Socrates
heLen
>Woody (TMW) wrote:Exactly so.
> > I get/assign the generator values from the client as well. The main reason
> > is that you have immediate access to the value for use in creating child
> > records, etc. If the value is assigned at the server, you have to re-query
> > in order to obtain the value.
>
>I do the same for master-detail relationships, but for simple tables I
>use triggers.
>
>In fact, the trigger approach isn't good for master-detail stuff at all.
>If you use triggers and insert master record, there is no way of finding
>out what is the PK value of master record, since generators are out of
>transaction control and some other user may increase it while your
>transaction is still running.
Something that should be clear to "newbies" is that, once you have got the
generator value, nobody else can get that value - PROVIDED that you do not
have SPs or user utilities that tinker with the generator. The
independence of generators is what allows you to work this way -- unlike
"identity" or "autoinc" types, you don't need to create the row in order to
get the PK value.
For M-D, fetch the generator value for the master's PK into a program
variable *before* you begin to create the master. But *also* have the "if
null then generate" trigger in the database to make the maintenance of the
generator independent of anyone's application code, manual tinkerings with
utility tools, batch inserts or whatever.
-----------------------------------------------------
I think this thread has lost sight of Lee's original question. His problem
seems to be that he is importing rows that *already* have values for the
PK, which is causing key violations against existing record keys. It's not
generators, per se, that cause this problem - it's inadequate (or lack of)
design of keys for replication purposes.
This is a design problem that will NEVER be solved by tinkering with the
generators.** If you have to read a key value or a generator value in
order to work out what the next key should be, then you have a potentially
destructive, major design flaw.
** "No design should ever tinker with generators" - Socrates
heLen