Two things off the top of my head.

Inside the trigger have an if schemaname = USER then fire.

Or if <> USER (or hard code it). USER is a key word.

Also, you could avoid using db_links couldn't you? If one schema has
granted permissions, and the other has a synonym to shema1.table_name.


-----Original Message-----
From: Huang Po-Tsang
[mailto:oracledba-ezmlmshield-x3040050.[Email address protected]
Sent: Tuesday, August 30, 2005 1:23 PM
To: LazyDBA Discussion
Subject: trigger on synonym

Hi,



I have some question regarding the trigger on the synonym. Here is the
scenario.

I have two schemas on the same Oracle db instance (name it "myDB").
Let's say "schema1" and "schema2".

I have a table, say "Table1" in the "schema1". In the "schema2", I
created a synonym, say "syn_Table1", for "schema1.[Email Address Removed] have granted all the privileges to "schema2" for the "Table1" in
"schema1". After that I created a trigger "syn_trigger" on the
"syn_Table1" synonym in [Email Address Removed] The trigger will fire when there
is a row inserted to the "syn_Table1". There is no trigger for "Table1"
in [Email Address Removed] The way I want the "syn_trigger" to fire is only if I
login to [Email Address Removed] and insert a row to "syn_Table1" but not when I
login to [Email Address Removed] and insert a row into "Table1". The problem now is
the "syn_trigger" will fire when login either to [Email Address Removed] or
[Email Address Removed] and insert a row in to the table or synonym. Is there a way
to control the trigger to fire only when I login to [Email Address Removed] and
insert a row to "syn_Table1"?





Thanks,



Potsang



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page