Re: Determining calling database via dblink

Jay wrote:

> (note: I didn't design this, I'm trying to fix it) Basically there is a
> common function with gets a nextval from various sequences. this function
> is called from all these different triggers. this function was resident on
> every database in the environment, except the "area" code changed from
> database to database, this "area" code was prepended to the sequence and
> used as the primary key during inserts. I want to centralize all these
> tables and this function. So I need to determine dynamically which database
> is the originating database for the transaction.
>
> -J
> "Mark D Powell" <mark.powell_at_eds.com> wrote in message
> news:178d2795.0208270522.60fac9ce_at_posting.google.com...
> > "Jake" <me_at_heyjay.com> wrote in message
> news:<akegk8$bjl$1_at_bob.news.rcn.net>...
> > > Hi,
> > >
> > > I have a setup where I have 2 remote databases and a local database. I
> have
> > > table1 on my local database, table1 has a before insert trigger which
> calls
> > > a function. My remote databases have views which are simple select *
> from
> > > table1_at_local_db.
> > >
> > > How can I determine which database is inserting to table1?
> > >
> > > Ideally, I'd like to use something like sys_context and get the
> global_name
> > > of the database which initiated the transaction. I thought of using
> > > SYS_CONTEXT ('USERENV','IP_ADDRESS'), but then that will require I keep
> a
> > > list of IP addresses and do the crossref, and if we move a server I'll
> > > forget and my app will blow up. Furthermore (since I haven't had a
> chance
> > > to test this yet) I don't even know if I'll get the remote db's IP in
> this
> > > situation.
> > >
> > > Any ideas? Help?
> > >
> > > Thanks
> > > J
> >
> > J, why do you really care where the insert came from?
> >
> > If you really need to track this then are you using database links
> > that contain an imbedded userid and password. If you are then setup a
> > different userid/password combination for each link so now your insert
> > trigger can capture the Oracle userid and that tells you where the
> > insert came from and would not change with db server relocation.
> >
> > Otherwise I would go back to looking at what sys_context gives you.
> >
> > HTH -- Mark D Powell --

My resonse to what you've written is to suggest a re-reading of a book on data normalization.

Architecture and design-wise this makes no sense.

Daniel Morgan Received on Tue Aug 27 2002 - 12:56:39 CDT