I would put a dbms_output message in it and test it, then let the formum
know :)
Lets do a scenario: Lets say an application never logs directly into
schema_a. But instead logs into the 'application_user' schema and has
rights on procedures or tables that allow it to insert data into
schema_a.table_a.
Inside trigger_a for table_a, you could state:
IF (USER != '<schema_a>') THEN
IF (:old.REC_IND = 'D') THEN
err := -20006;
msg := 'my message';
RAISE it;
END IF;
END IF;
Etc..
I stretching the example a little, by stating that in this case the
'application' logged into the 'appication_user' schema <> 'schema_a'.
Therefore the following logic will be executed.
If however, you where indeed logged in as 'schema_a', then it would be
skipped. This is a good way to be able to do data fixes, or by pass the
triggers as a DBA or someone with 'schema_a's password. Yet everyone
else has to fire the trigger.
The exact logic and implementation of course would be corrected for your
scenario.
-----Original Message-----
From: Huang Po-Tsang
[mailto:oracledba-ezmlmshield-x43043640.[Email address protected]
Sent: Tuesday, August 30, 2005 3:54 PM
To: LazyDBA Discussion
Subject: RE: trigger on synonym
Thanks.
One more thing, for this " if schemaname = USER", is there a quick way
to know what the schemaname that the trigger belongs to inside this
trigger?
-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x48067018.[Email address protected]
Sent: Tuesday, August 30, 2005 2:28 PM
To: LazyDBA Discussion
Subject: RE: trigger on synonym
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
--------
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
--------
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