> Hi Alan!
>
> This answer is in a new thread since you hijacked an existing thread.
> I'm not a system table guru, hence, I'll only ask you one question.
>
> How would SQL figure which generator should be applied to which table
> and field, i.e. why match GENNAME1 with TABLE1.ID and not TABLE2.AGE?
> Have you added your own table with GENERATORNAME, TABLENAME and
> FIELDNAME so the query doesn't have to guess?

that's exactly what I've done now - system tables don't quite have enough -
or if they do, the route is too circuitous to achieve it.
All I have to do is register the tablename, genname and pkfield field in the
table whenever I create a new table.

Alan

>
> Having such a table, it shouldn't be too difficult to write a program
> that went through the table and generated a script to be run on that
> database. Without this table linking generators and fields, I think
> you'll have to analyse the source of your triggers - something I doubt
> will be very easy.
>
> Set
>
> Alan wrote:
> I have an interesting task...
> I have a database with generators initially set to 1 and increment by
> 10 (other dbs in the set of 9 have gens set to 2, 3, 4 etc to 9 and
> all increment by 10). They replicate on that basis so there is never
> a collision. I can always tell a record which is created on server 4,
> for instance.
>
> Now I want to establish a regime where whenever I need to grab a copy
> of the one of the DBs to re-create a DB for another server (a corrupt
> or crashed box), I need to reset all the generators for the db for
> that box.
> e.g.
> SELECT MAX(ID) FROM MYTABLE
> WHERE F_RIGHT(CAST(ID AS VARCHAR(32)),1)=1
> would reveal the new generator value for this table for server 1.
> Each server would give me this same answer).
>
> What would be great is a View which would make this query result with
> the name of the corresponding tablename
> GENNAME1 TABLENAME1 17881
> GENNAME2 TABLENAME2 200001
> GENNAME3 TABLENAME3 12671
> etc
>
> I could then construct a select statement which could
> SELECT 'set generator GENNAME1 TO '||CAST(VALUE AS VARCHAR(32))||';'
> FROM VIEW
>
> and I would have - 'hey presto' a set of SQL statements which would
> instantly reset the db ready for deploying to the missing box. I could
> backup any db, restore it to the new box, run the SQL and go live
> again for replication.
>
> Any ideas for this system oriented VIEW from any system table gurus?
> thanks
> Alan
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>