Linking of Schemas

[amitpatel66]

Wow! does it mean if I have 15 schemas, it will be 15 tables? Can you teach me how to do that please.

Thanks.

You need to find how many tables are being used in each schema after which you can decide on what can be done. Ofcourse having lots of triggers is not advisable

[Pilgrim333]

He means lots of triggers per table. If you have a few triggers per table, that is acceptable.

Pilgrim.

[amitpatel66]

He means lots of triggers per table. If you have a few triggers per table, that is acceptable.

Pilgrim.

I understood what @OP said and I had explained him.

@OP,

Its question of, if there are 10 tables in each schema used by application, then 10 * 15 = 150 tables and creating 150 triggers one each for a table is ofcourse not smart way of implementing.

And again you are going to connect to database from an application, and insert a record in the table which in turn will fire a trigger. So you need to make sure if this way you are reallly getting unique value and not the old value.

[Pilgrim333]

@OP

The whole idea of the trigger is that the trigger will fetch from the sequence and not the application. The unique values should not be a problem.
The whole idea of triggers is that, no matter from which interface you want to access the tables, everything is done on the database side, so the interface need not worry about unique values, bussiness rules etc. This is the Oracle way of solving the problem you are encountering.

As i stated before, he has to test the code in the trigger before implementing. Furthermore, if you have a system that has 150+ tables, then 150+ triggers should be no problem at all. If it is, then your system is way too big for your budget.

Pilgrim.

[amitpatel66]

@OP

The whole idea of the trigger is that the trigger will fetch from the sequence and not the application.

Ofcourse the application is going to connect to the database and perform some insert so that the trigger gets invoked. If there was no application at all then the sequence was working perfeclty from sqlplus

As i stated before, he has to test the code in the trigger before implementing. Furthermore, if you have a system that has 150+ tables, then 150+ triggers should be no problem at all. If it is, then your system is way too big for your budget.

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.

[Pilgrim333]

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.

[amitpatel66]

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.

[amitpatel66]

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??

[Pilgrim333]

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.

[amitpatel66]

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.

[Pilgrim333]

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.

[jam26]

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

[jam26]

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

[Pilgrim333]

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:

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.

[jam26]

Thank you very much Pilgrim. I made a DB link successfully and its now working. :-)


Jam