/*
 * Title  : syn_trg.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Synonym trigger to avoid synonyms.
 * History: N/A
 */

set verify off

accept p_source char prompt "Enter Source Schema Name: "
accept p_target char prompt "Enter Target Schema Name: "

CREATE OR REPLACE TRIGGER &&p_target..SYN_TRG AFTER LOGON ON &&p_target..SCHEMA 
DECLARE
BEGIN
	EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '|| UPPER('&&p_source');
EXCEPTION
	WHEN OTHERS THEN raise_application_error(-20001, 'Error: ' || SQLERRM );
END ;
/
