Re: trigger is firing in wrong schema

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
Received on Tue Jun 27 2006 - 13:35:18 CDT