Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: trigger is firing in wrong schema

Re: trigger is firing in wrong schema

From: joel garry <joel-garry_at_home.com>
Date: 29 Jun 2006 16:48:56 -0700
Message-ID: <1151624936.828689.75170@y41g2000cwy.googlegroups.com>

SamuelReyna wrote:
> Hi, did you ever find a solution to your problem. I'm having the exact
> same problem. We have multiple schemas representing our different
> environments (dev, beta, staging, etc.). Any ideas? Thanks in advance
> for your assistance.
>
> SergeNik wrote:
> > Thanks for reply. We are running on pretty latest release
> > 10.2.0.2
> >
> > The issue is not reproducible, that is why I can not create a tar with
> > oracle support. I am not able to generate a test case for this reason.
> > In a nutshell, it goes like this:
> >
> > 1.execute procedure in schema1
> > 2.Above procedure has insert into table schema1.table1. schema1.table1
> > has trigger1
> > 3.It works most of the time, but sometimes, we would get the error:
> > Which suggests that
> > step #2 is firing a trigger belonging to table schema2.table2. The
> > trigger has the same name in both schemas. In fact all objects are
> > named the same in both schemas. The only difference is schema name
> > itself. There are no hardcoded references in triggers/procedures or
> > packages in any of the code.
> >
> >
> > Vladimir M. Zakharychev wrote:
> > > SergeNik wrote:
> > > > Hello, I have a weird problem. I have 4 database schemas.
> > > >
> > > > 1st set
> > > > 1 schema has parent objects and 1 schema has synonyms to point to these
> > > > objects
> > > >
> > > > 2nd set has same set up but with different names
> > > >
> > > >
> > > > I have a procedure in 2nd set of schemas, which is performing an insert
> > > > into a table. That table has a trigger with the same name in both of
> > > > the parent schemas. The trigger is somehow firing referencing into the
> > > > 1st parent schema and failing with parent key not found because that
> > > > data does not exist in that schema. What is more peculiar is that it
> > > > happens randomly and sometimes the transaction succeeds (with the same
> > > > set of values) and sometimes it fails.
> > > >
> > > > I checked all the synonym names and grants and hard coded references to
> > > > the schemas. We have all of them set correctly and there are no hard
> > > > coded schema references anywhere. I am sure this is an "oracle
> > > > feature". If anyone has any exp with this please help. Thanks a lot
> > >
> > > Oracle version? Can you provide a worked example? Can you reproduce
> > > this behavior at will? Do you run the latest patchset for your release?
> > > It sounds more like a defect than a "feature," so if you're not at the
> > > latest patchset level, try to patch and see if the issue persists. If
> > > it does and you can set up a reproducible test case, then this
> > > issue is for Oracle Support to deal with.
> > >
> > > Regards,
> > > Vladimir M. Zakharychev
> > > N-Networks, makers of Dynamic PSP(tm)
> > > http://www.dynamicpsp.com

Try cronning a command like: select owner, object_name,subobject_name, object_id, data_object_id,created, last_ddl_time, status from dba_objects
where object_type = 'TRIGGER'
and object_name like '%part of your objects name%' order by object_name, owner
/

I'm wondering if you will see some invalids from time to time, perhaps something is making one invalid or latched so the other is being found.  Maybe you are granting privileges for someone on one trigger while someone else is using it? Maybe you are inadvertently using public synonyms instead of private? Maybe check for INVALID status for object_type SYNONYMS in dba_objects? Are there any errors in the alert log? Are you using dblinks? Any non-standard cursor parameters?

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20060629/news_1b29amcc.html
Received on Thu Jun 29 2006 - 18:48:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US