I need to create a trigger that alter the session of initial schema(FIRST_USER_SCHEMA) to logged on second schema (SECOND_USER_SCHEMA) which executes some stored procedures on single table on other schema(THIRD_USER_SCHEMA). I took care of flow from SECOND_USER_SCHEMA to THIRD_USER_SCHEMA by granting privileges and executing stored procedures and updating the single customers table etc. First part of creating trigger which ALTERS and SETS session is pending.
I wrote trigger like this and executed.
CREATE OR REPLACE FIRST_USER_SCHEMA_NAME.TRIGGER_NAME
EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA=SECOND_USER_SCHEMA_NAME');
It is giving error saying...ORA-30506..system triggers cannot be based on tables or view..
Once user logs on to SECOND_USER_SCHEMA_NAME he has privileges to access the stored procedures which update a single table customers in schema named THIRD_SCHEMA_NAME. How to write this trigger.
Any sample code, ideas, resources, links highly appreciated. Thanks in advance.