--- In firebird-support@yahoogroups.com, Radu Sky <skysword76@...> wrote:

>
> Ivan Setya Darmawan wrote:
> > Hi,
> >
> > Assume I have 2 branch offices. One at Northpole and the other at
> > Southpole (brrbb). Each of them has a same customer table with fields
> > like this:
> >
> > customers
> > ----------------------
> > cust_id VARCHAR(20)(PK)
> > cust_name VARCHAR(80)
> >
> > And now has come consolidation day. I need to merge northpole customer
> > table and southpole customer table to a single customer table in
> > headquater database.
> >
> > I think, using autoincrement for cust_id is not a choice, coz it could
> > arise PK constraint error or cust_id replaced with new increment
> > value. So, I decide using before insert trigger for cust_id for each
> > table (including customers table at headquarter) like this:
> >
> > BEGIN
> > /* Trigger body */
> > IF (NEW.CUST_ID IS NULL) THEN
> > NEW.CUST_ID = RAND();
> > END
> >
> > and I hope merging those table become visible.
> >
> > Do I on right track here or anyone has many better ideas?
> >
> > TIA
> > #Ivan Darmawan
> >
>
> Hello,
>
> An usual solution to your problem might be that one table PK starts

at 1

> and the other at 1.000.000.000 or whatever number is comfortable to you.
> Other solution is to have one PK as 'A'||<some_generator_value> and the
> other as 'B'||<some_generator_value>.
>

Ok, adding dept_id field to the table:

customers
----------------------
cust_id VARCHAR(20)(PK)
cust_name VARCHAR(80)
dept_id INTEGER

and then modify the trigger to:

BEGIN
/* Trigger body */
IF (NEW.CUST_ID IS NULL) THEN
NEW.CUST_ID = NEW.DEPT_ID || RAND();
END

is it better?

TIA
#Ivan Darmawan