Helen,

Thanks for your comments about my trigger. You are right.
This code is dangerous. It works perfectly in the circumstance
that I developed it for where I have a some tables still in Paradox
that are merged into the IB tables each night. This updates
the generators on those tables. The tables that are updated
from Paradox are only selected from in interbase.

I guess I need to rethink my Triggers - and remove this extra
code when the table is started to be used from Interbase.

I am in the process of changing a application from Paradox
based to Interbase.

Helen, Thanks again, and everyone else sorry for the bad code.

Michael L. Horne

> -----Original Message-----
> From:
> sentto-402930-3505-981586291-guardian=pobox.com@...
> [mailto:sentto-402930-3505-981586291-guardian=pobox.com@....
> com]On Behalf Of Helen Borrie
> Sent: Wednesday, February 07, 2001 5:49 PM
> To: IBObjects@yahoogroups.com
> Subject: RE: [IBO] GeneratorLinks in 5,000,000 words or less
>
>
> At 04:11 PM 07-02-01 -0500, you wrote:
> >Hello Paul
> >
> >GeneratorLinks is to allow IBO to go ahead and get the
> >ID of the record before the actual insert. This allows
> >it to pass it along to Detail tables that could be connected
> >and other things. You don't have to use it for positioning,
> >I think you are talking about keylinks.
> >
> >I use the following trigger on my tables:
> >
> > create trigger TRGBEFINS_Customer for Customer
> > active before insert position 0
> > as
> > DECLARE VARIABLE Temp_ID integer;
> > begin
> > if ((new.Cus_ID is NULL) or (new.cus_ID = 0)) then
> > begin
> > new.Cus_ID = gen_id (Gen_Customer_ID, 1);
>
> END THE TRIGGER HERE !!!
>
> The following code is !!!! dangerous !!!!. Generators operate right
> outside the transaction context. In a multi-user environment, you are
> certain to get PK violations. Don't do it!!!
>
> > end else begin
> > Temp_ID = gen_id(Gen_Customer_ID,0);
> > if (new.CUS_ID > Temp_ID) then
> > begin
> > Temp_ID = gen_id(Gen_Customer_ID, new.Cus_ID-Temp_ID);
> > end
> > end
> > end
> >
> >It will update your generator if necessary and also, make
> >sure that the generator doesn't get behind if you inserted
> >records that had IDs not derived from the generator.
>
> If you are using a generator to populate a primary key, you should NEVER
> try to insert rows where the PK comes from another source that **that**
> generator.
>
> To add a further note on GeneratorLinks, you should always use it when
> inserting into a bi-directional dataset, rather than waiting until after
> the insert has committed, to ensure that the KeyLinks will work
> properly. Reserve the use of the trigger for external clients or scripts.
>
> Helen
>
>
>
>
> All for Open and Open for All
> InterBase Developer Initiative · http://www.interbase2000.org
> _______________________________________________________
>
>
>
>