469,326 Members | 1,414 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Linking of Schemas

19
Hello,

Is there anybody can help me on how can I make an auto number across all my 15 schemas. I need to create it for tagging of my equipment.

Thanks,
Jam
Nov 13 '08
60 4045
Pilgrim333
127 100+
If there was no application at all then the sequence was working perfeclty from sqlplus
There is no problem with the sequence, with or without the application.

Budget does not come in to play here. The application is already available. It need to be customized by including the functionality of having a auto number generation across multiple schemas of a database. And yes why not an application have more than 150 tables if there are 15 schemas involved. Its just an assumption and nothing else which can be clarified if @OP answers the question of letting know to how many tables are used in each schema by the application.
Budget is meant, if your database can't handle 150 triggers, then you should get a more powerful system.
The application should not be customized, the autonumbering should happen at the database side. Even if the database has a 1000+ tables.

Pilgrim.
Nov 19 '08 #51
amitpatel66
2,367 Expert 2GB
It doesnt make sense creating 150 odd triggers. What if in future, number of table increases, one cannot keep creating a trigger.

If and only if the number of tables are less (totalling all the tables across 15 schemas that are used by an application) and there will not be any new table inclusion in future then creating a trigger is acceptable. Again it have to be tested before it could work.
Nov 19 '08 #52
amitpatel66
2,367 Expert 2GB
There is no problem with the sequence, with or without the application.

Pilgrim.
There is a problem with the sequence when accessed from application, then what are be discussing here for so so long??
Nov 19 '08 #53
Pilgrim333
127 100+
There is a problem with the sequence when accessed from application, then what are be discussing here for so so long??
The application is not accessing the sequence, the poster just updated the autokey of the application once with the value of the sequence and expected that it would automatically link it with the sequence.

So, there is no problem with the sequence when accessed from the application, because the application is not accessing the sequence.

Pilgrim.
Nov 19 '08 #54
amitpatel66
2,367 Expert 2GB
The application is not accessing the sequence, the poster just updated the autokey of the application once with the value of the sequence and expected that it would automatically link it with the sequence.

Pilgrim.
This doesnt mean that the sequence is accessed from an application? If you really dont want to make the sequence work from application then why should it be linked to an application?

Anyways I can utilize this time and rethink of any other solution for this issue rather just increasing the size of this thread, becuase its important that @OP understands what I am trying to say.
Nov 19 '08 #55
Pilgrim333
127 100+
It isn't accessed from the application by the autokey.

@OP

Meanwhile, in googley land...

It seems that this problems is something more people have. Someone posted it on his blog, hopefully it will help you, and you need not go through all the trouble of making (all) the triggers.

Maximo and unique id's

Pilgrim.
Nov 19 '08 #56
jam26
19
Hello Experts,

I already made to link all my schemas to the sequence using Trigger. This may not be possible without your help.

Thank You Very Much!!!!

Jam
Nov 20 '08 #57
jam26
19
Hello Experts,

Now that I have connected the sequence across all schemas, Is it possible that I connect my other database on this sequence using trigger?

Thanks,
Jam
Nov 24 '08 #58
Pilgrim333
127 100+
Hi,

I don't know what you mean. It is possible to use the sequence for numbering in tables in another database. The sequence can be used through a database link. Syntax for that is:

Expand|Select|Wrap|Line Numbers
  1. select <sequenceschema>.<seqname>.nextval@<dblink> from Dual ; 
If you just want to insert data in tables in the database where the sequence resides, from other databases and use the sequence, that is no problem. It doesn't matter from where you insert the data in the tables, because you implemented the use of the sequence in the triggers of the tables, the sequence will always be used.

Pilgrim.
Nov 24 '08 #59
jam26
19
Thank you very much Pilgrim. I made a DB link successfully and its now working. :-)


Jam
Nov 26 '08 #60
Pilgrim333
127 100+
Good to hear Jam, congrats on getting it working.

A good lesson, when implementing bussiness rules, autonumbering etc. that needs to be done on a table, always implement it in the table triggers. This way you ensure that these necessities will always be met, no matter what interface is used for manipulating data.

If you have more issues, please let us know.

Pilgrim.
Nov 26 '08 #61

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Roderick A. Anderson | last post: by
4 posts views Thread by anonymous | last post: by
2 posts views Thread by John Jenkins | last post: by
6 posts views Thread by Dennis Gearon | last post: by
3 posts views Thread by Sami Marzouki | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.