Add Trigger to remove deleted table from SDO_GEOM_METADATA and MAPINFO_CATALOG

 

CREATE OR REPLACE TRIGGER "MAPINFO"."DROP_SPATIAL_TABLE"
BEFORE DROP OR TRUNCATE
ON SCHEMA
DECLARE
TABLE_IN_CAT  NUMBER(5);
TABLE_IN_META  NUMBER(5);
BEGIN
 IF (ORA_DICT_OBJ_TYPE = 'TABLE') THEN
   SELECT COUNT(*) INTO TABLE_IN_CAT FROM "MAPINFO"."MAPINFO_MAPCATALOG" WHERE TABLENAME=ORA_DICT_OBJ_NAME;
   IF (TABLE_IN_CAT > 0) THEN
     DELETE FROM "MAPINFO"."MAPINFO_MAPCATALOG" WHERE TABLENAME=ORA_DICT_OBJ_NAME;
   END IF;
   SELECT COUNT(*) INTO TABLE_IN_META FROM "MDSYS"."USER_SDO_GEOM_METADATA" WHERE TABLE_NAME=ORA_DICT_OBJ_NAME;
   IF (TABLE_IN_META > 0) THEN
     DELETE FROM "MDSYS"."USER_SDO_GEOM_METADATA" WHERE TABLE_NAME=ORA_DICT_OBJ_NAME;
   END IF;
     END IF;
END;