TRIGGER: MDSYS.SDO_PREFERRED_OPS_SYS_TRIGGER

Source

Description

MDSYS.SDO_PREFERRED_OPS_SYS_TRIGGER
BEFORE
  INSERT OR
  UPDATE
ON
  MDSYS.SDO_PREFERRED_OPS_SYSTEM
FOR EACH ROW

Type

BEFORE EACH ROW

Event

INSERT OR UPDATE

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

DECLARE
  c NUMBER;
BEGIN

  MDSYS.sdo_cs.sdo_cs_context_invalidate;

  SELECT COUNT(COORD_OP_ID) INTO c FROM MDSYS.SDO_AVAILABLE_OPS WHERE COORD_OP_ID = :new.COORD_OP_ID;
  IF(c < 1) THEN
    IF(:new.COORD_OP_ID > 0) THEN
      MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, ('Operation ' || :new.COORD_OP_ID || ' does not exist.'));
    ELSE
      SELECT COUNT(COORD_OP_ID) INTO c FROM MDSYS.SDO_AVAILABLE_OPS WHERE COORD_OP_ID = -:new.COORD_OP_ID;
      IF(c < 1) THEN
        MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, ('Operation ' || :new.COORD_OP_ID || ' does not exist, neither does its forward version ' || -:new.COORD_OP_ID || '.'));
      ELSE
        SELECT COUNT(COORD_OP_ID) INTO c FROM MDSYS.SDO_AVAILABLE_NON_ELEM_OPS WHERE COORD_OP_ID = -:new.COORD_OP_ID;
        IF(c < 1) THEN
          MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, ('Elementary operation ' || -:new.COORD_OP_ID || ' is not reversible.'));
        ELSE
          dbms_output.put_line('Concatenated operation ' || -:new.COORD_OP_ID || ' is not reversible, because of its following elementary component(s):');

          <>
          DECLARE
            CURSOR CULPRITS IS
              SELECT
                -PATHS.SINGLE_OPERATION_ID "COORD_OP_ID"
              FROM
                MDSYS.SDO_COORD_OP_PATHS PATHS
              WHERE
                PATHS.CONCAT_OPERATION_ID = -:new.COORD_OP_ID
              MINUS
              SELECT
                COORD_OP_ID
              FROM
                MDSYS.SDO_AVAILABLE_OPS;
            CULPRIT CULPRITS%ROWTYPE;
          BEGIN
            FOR CULPRIT IN CULPRITS LOOP
              dbms_output.put_line('Elementary operation ' || (-CULPRIT.COORD_OP_ID) || ' is not reversible.');
            END LOOP;
          END FIND_CULPRITS;

          MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, ('Concatenated operation ' || -:new.COORD_OP_ID || ' is not reversible.'));
        END IF;
      END IF;
    END IF;
  END IF;

  SELECT IS_IMPLEMENTED INTO c FROM MDSYS.SDO_AVAILABLE_OPS WHERE COORD_OP_ID = :new.COORD_OP_ID;
  IF(c = 0) THEN
    MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, ('Operation ' || :new.COORD_OP_ID || ' is not implemented.'));
  END IF;
END;