alter session set current_schema=MDSYS;
create or replace trigger sdo_sem_drop_trigger
before drop on database
declare
  type vcurType is REF CURSOR;
  vcur vcurType;
  stmt VARCHAR2(1000);
  stm2 VARCHAR2(250);
  rdt  VARCHAR2(80);
  rsid number;
  cnt  number;
  m_id number;
  model varchar2(100);
  rdf_exception EXCEPTION;
  pragma exception_init(rdf_exception, -55331);

  e_cannot_multi_mod EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_cannot_multi_mod, -30512);
begin
  
  

     if sys.dbms_standard.dictionary_obj_type = 'USER' THEN
       BEGIN
         EXECUTE IMMEDIATE
          'begin ' ||
            'mdsys.rdf_apis_internal.' ||
            'notify_drop_user( :don ); ' ||
          'end;' 
             USING sys.dbms_assert.QUALIFIED_SQL_NAME(sys.dbms_standard.dictionary_obj_name);

         EXCEPTION
           WHEN rdf_exception THEN RAISE;
           WHEN OTHERS        THEN NULL;
       END;
       RETURN;
    end if;

  if (sys.dbms_standard.dictionary_obj_type <> 'TABLE') then
    return;
  end if;

  
  
  if (sys.dbms_standard.dictionary_obj_owner = 'MDSYS') then
    if (sys.dbms_standard.dictionary_obj_name like 'RDF_%') or
       (sys.dbms_standard.dictionary_obj_name like 'RDF#%') then
      return;
    end if;
  end if;

  stmt := 'select count(*) from all_views ' ||
          ' where owner = ''MDSYS'' and view_name = ''RDF_MODEL$'' ';
  execute immediate stmt into cnt;
  if (cnt = 1) then
    begin

      cnt := 0;
      stmt := 'SELECT count(*) FROM MDSYS.RDF_MODEL$ ' ||
              ' WHERE OWNER = :1 AND TABLE_NAME = :2';
      begin
        execute immediate stmt into cnt using sys.dbms_standard.dictionary_obj_owner,
                                              sys.dbms_standard.dictionary_obj_name;
      exception
        when e_cannot_multi_mod then
          
          
          
          
          
          if (substr(sys.dbms_standard.dictionary_obj_name, 1, 4)='BIN$' or
              substr(sys.dbms_standard.dictionary_obj_name, 1,13)='SYS_IOT_OVER_') then
            return;
          else
            raise;
          end if;
      end;
   
      

      if (cnt > 0)  then
        raise_application_error(-20000,
                                'Cannot drop table "' || sys.dbms_standard.dictionary_obj_name ||
                                '" because this table owns RDF objects');

        
        
        
        
        
        
        
        
      end if;
    end;
  end if;
end;
/
show errors;
create or replace trigger sdo_sem_alter_trigger
before ALTER on database
declare
  type vcurType is REF CURSOR;
  vcur vcurType := null;
  stmt VARCHAR2(1000);
  stm2 VARCHAR2(250);
  rdt  VARCHAR2(80);
  rsid number;
  cnt  number;
  m_id number;
  model varchar2(100);
  cname varchar2(32); 

  e_cannot_multi_mod EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_cannot_multi_mod, -30512);
begin
  
  

  if (sys.dbms_standard.sysevent <> 'ALTER' or sys.dbms_standard.dictionary_obj_type <> 'TABLE') then
    return;
  end if;

  if (sys.dbms_standard.dictionary_obj_owner = 'MDSYS') then
    if (sys.dbms_standard.dictionary_obj_name like 'RDF_%') or
       (sys.dbms_standard.dictionary_obj_name like 'RDF#%') then
      return;
    end if;
  end if;

  stmt := 'select count(*) from all_views ' ||
          ' where owner = ''MDSYS'' and view_name = ''RDF_MODEL$'' ';
  execute immediate stmt into cnt;
  if (cnt = 1) then
    begin
      cnt := 0;
      stmt := 'SELECT count(*) FROM MDSYS.RDF_MODEL$ ' ||
              ' WHERE OWNER = :1 AND TABLE_NAME = :2';
      begin
        execute immediate stmt into cnt using sys.dbms_standard.dictionary_obj_owner,
                                              sys.dbms_standard.dictionary_obj_name;
      exception
        when e_cannot_multi_mod then
          
          
          
          
          
          if (substr(sys.dbms_standard.dictionary_obj_name, 1, 4)='BIN$' or
              substr(sys.dbms_standard.dictionary_obj_name, 1,13)='SYS_IOT_OVER_') then
            return;
          else
            raise;
          end if;
      end;
   
      

      if (cnt > 0)  then
        open vcur for 'SELECT model_name, model_id, column_name ' ||
                      '  FROM MDSYS.RDF_MODEL$ ' ||
                      ' WHERE OWNER = :1 AND TABLE_NAME = :2' 
          using sys.dbms_standard.dictionary_obj_owner, sys.dbms_standard.dictionary_obj_name;
        loop
          fetch vcur into model, m_id, cname;
          exit when vcur%NOTFOUND;
          

          if (sys.dbms_standard.is_drop_column(cname) or sys.dbms_standard.is_alter_column(cname)) then
            

            
            
            if (vcur%isopen) then
              close vcur;
            end if;
            raise_application_error(-20000,
                                    'Cannot alter or drop column "' || cname ||
                                    '" because this column owns RDF objects');
          end if;
        end loop;

        close vcur;
      end if;
    end;
  end if;
end;
/
show errors;