(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 --
Received on Tue Aug 27 2002 - 12:18:58 CDT
