TRIGGER BEFORE DROPPING AND REPLACING AN EXISTING SYNONYM

Before Drop:

CREATE OR REPLACE TRIGGER 'ABC'
BEFORE DROP
ON DATABASE
DECLARE
GRANTED_ROLE VARCHAR2(30);
X_GRANTED_ROLE VARCHAR2(100);
temp_cnt NUMBER  := 0 ;
BEGIN

select count(*)
INTO temp_cnt
from dba_users
where profile LIKE 'APPLICATION%' --- here anyone can drop except, users with Application profile                                    
and username = ora_dict_obj_owner ;  

if ( temp_cnt > 0 )
    THEN
    IF SYS.DICTIONARY_OBJ_TYPE = 'SYNONYM' THEN
        RAISE_APPLICATION_ERROR(-20998, 'Public Synonym Cannot be dropped. Contact DBA');
    END IF;
  END IF ;
  END;
/


Before Replace: (Can create a new synonym, but cannot replace an existing synonym)


1)
CREATE OR REPLACE TRIGGER 'XYZ'
  before create on database
declare
    temp_cnt NUMBER := 0 ;

  function syn_exists
    return boolean is
    v_dummy   varchar2 (1);
    raise_application_error varchar2(30);
  begin
    select null
    into   v_dummy
    from   dba_synonyms
    where      owner = 'PUBLIC'
           and synonym_name = ora_dict_obj_name;

    return true;
  exception
    when no_data_found then
      return false;
  end syn_exists;
begin

select count(*)
INTO temp_cnt
from dba_users
where profile LIKE 'APPLICATION%'  ---- for certain users in DB
and username = ora_dict_obj_owner ;  

if ( temp_cnt > 0 ) and ( ora_dict_obj_type = 'SYNONYM' ) and (syn_exists ) then
        raise_application_error ( -20000, 'Public Synonym ' || ora_dict_obj_name || ' aready exists. Cannot replace it. Contact DBA');
end if ;

end;
/

'OR'

2)

CREATE OR REPLACE TRIGGER '123'

  before create on database

DISABLE

declare

  function syn_exists

    return boolean is

    v_dummy   varchar2 (1);

    raise_application_error varchar2(30);

  begin

    select null

    into   v_dummy

    from   all_synonyms

    where      owner = ora_dict_obj_owner

           and synonym_name = ora_dict_obj_name;

    return true;

  exception

    when no_data_found then

      return false;

  end syn_exists;

begin

  if ora_dict_obj_type = 'SYNONYM' then

    if syn_exists then

      if not dbms_session.is_role_enabled ('DBA') then --- for all users in DB

        raise_application_error ( -20000, 'Synonym ' || ora_dict_obj_name || ' aready exists, attempt to replace contact DBA');

      end if;

    end if;

  end if;

end;

/