Re: Triggers and Multiple Schemas.

Paul Newman wrote:
<blockquote
 cite="midD5F7521105A39145BEA6A6F47AEFFA8837B9BE@sbserver.tripoint.local"
 type="cite">



<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
    {margin:0cm;
    margin-bottom:.0001pt;
    font-size:12.0pt;
    font-family:"Times New Roman";}
a:link, span.MsoHyperlink
    {color:blue;
    text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
    {color:purple;
    text-decoration:underline;}
span.EmailStyle17
    {mso-style-type:personal-compose;
    font-family:Arial;
    color:windowtext;}
@page Section1
    {size:612.0pt 792.0pt;
    margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
    {page:Section1;}
-->


  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Hi,
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> We run with
multiple identical schemas in our db.
Each schema actually represents a clients db. What we’d like to do is
have a common schema where trigger functions and the like are held
whilst each
trigger defined against the tables is in there own particular schema.
This would
mean that there is one function per trigger type to maintain.
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">However at
the moment we are placing the trigger
functions within each schema along with trigger itself. The reason is
that we don’t
know of a function or a variable that says “Give me the schema of the
trigger that is calling this function”. We are therefore having to
write
the function into every schema and then use set search_path =br1; as
the first
line. This is a real headache to us since we are intending on putting
200 –
300 schemas in one db.
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">My question
is … is there such a function or
variable ?  …. Or is there a better for us to achieve this ?
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Regards
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Paul Newman


Paul,
When you say "multiple identical schemas" are they all separate
explicit schemas?  Or are they all under a general 'public' schema.
From my understanding, when you create a new db instance, it's under
the public level schema by default unless you create an explicit schema
and subsequently a db instance - or several - therein, effectively
establishing sibling db instances belonging to a single schema, I know
at least that data in the form of table access is allowed across the
siblings.  I'd also assume that this would be the case for triggers and
functions that could be identified or defined at the 'root' level
schema.

Now I'm sure there is associated jargon with this type of hierarchical
or tiered schema layout, so please don't anybody shoot me because of my
analogy to 'root' level scenario.

I think this is a great opportunity for somebody to add additional
insight with their experience with utilizing explicit schemas, rather
than the default public schema.

We have to remember, that for every database instance, there is at
least one schema to which it belongs, meaning that a schema and is a db
container of sorts, there can be many database instances that exist in
1 schema to - typically public by default.

I know I'm opening up a big can of worms... but hey... let's have it ;)