I am attempting to write a table trigger which queries another table that's outdoors the schema in which the trigger will reside. Is possible? It appears like I've not a problem querying tables during my schema however i get:
Error: ORA-00942: table or view does not exist
when attempting attempting to query tables outdoors my schema.
Sorry because of not supplying just as much information as you possibly can to begin with. I had been of the opinion this was simpler.
I am trying produce a trigger on the table that changes some fields on the recently placed row in line with the information on some data that might maintain a table that's in another schema.
The consumer account that I am using to produce the trigger comes with the permissions to operate the queries individually. Actually, I have had my trigger print the query I am attempting to run and could run it by itself effectively.
I ought to also observe that I am building the query dynamically using the EXECUTE IMMEDIATE statement. Here's a good example:
CREATE OR REPLACE TRIGGER MAIN_SCHEMA.EVENTS BEFORE INSERT ON MAIN_SCHEMA.EVENTS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE rtn_count NUMBER := 0; table_name VARCHAR2(17) := :NEW.SOME_FIELD; key_field VARCHAR2(20) := :NEW.ANOTHER_FIELD; BEGIN CASE WHEN (key_field = 'condition_a') THEN EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_A.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count; WHEN (key_field = 'condition_b') THEN EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_B.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count; WHEN (key_field = 'condition_c') THEN EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_C.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count; END CASE; IF (rtn_count > 0) THEN -- change some fields that are to be inserted END IF; END;
The trigger seams to fail around the EXECUTE IMMEDIATE using the formerly pointed out error.
I've done more research and that i can provide more clarification.
The consumer account I am using to produce this trigger isn't Primary_SCHEMA or any of the OTHER_SCHEMA_Xs. The account I am using (ME) is offered rights towards the involved tables through the schema customers themselves. For instance (USER_TAB_PRIVS):
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY MAIN_SCHEMA ME MAIN_SCHEMA EVENTS DELETE NO NO MAIN_SCHEMA ME MAIN_SCHEMA EVENTS INSERT NO NO MAIN_SCHEMA ME MAIN_SCHEMA EVENTS SELECT NO NO MAIN_SCHEMA ME MAIN_SCHEMA EVENTS UPDATE NO NO OTHER_SCHEMA_X ME OTHER_SCHEMA_X TARGET_TBL SELECT NO NO
And That I possess the following system rights (USER_SYS_PRIVS):
USERNAME PRIVILEGE ADMIN_OPTION ME ALTER ANY TRIGGER NO ME CREATE ANY TRIGGER NO ME UNLIMITED TABLESPACE NO
Which is things i based in the Oracle documentation:
To produce a trigger in another user's schema, in order to reference a table in another schema from the trigger inside your schema, you'll want the CREATE ANY TRIGGER system privilege. With this particular privilege, the trigger could be produced in almost any schema and may be connected with any user's table. Additionally, the consumer creating the trigger must also provide EXECUTE privilege around the recommended methods, functions, or packages.
Here: Oracle Doc
Therefore it looks in my experience such as this should work, but I am unsure concerning the "EXECUTE privilege" it's mentioning to within the doc.