TRIGGER: MDSYS.SDO_ANNOT_TRIG_INS1

Source

Description

SDO_ANNOT_TRIG_INS1
INSTEAD OF INSERT OR DELETE OR UPDATE ON USER_ANNOTATION_TEXT_METADATA
REFERENCING NEW AS n OLD as o
FOR EACH ROW

Type

INSTEAD OF

Event

INSERT OR UPDATE OR DELETE

Column

When

Referencing

REFERENCING NEW AS N OLD AS O

Body

declare
 tname varchar2(32);
begin

  IF INSERTING THEN
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
      INSERT INTO SDO_ANNOTATION_TEXT_METADATA values(null,tname,
            nls_upper(:n.table_name), nls_upper(:n.column_name), :n.MAP_BASE_SCALE,
                 :n.TEXT_EXPRESSION,
             :n.TEXT_ATTRIBUTES);
  ELSIF UPDATING THEN
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
  UPDATE SDO_ANNOTATION_TEXT_METADATA SET(F_TABLE_SCHEMA,
             F_TABLE_NAME,F_TEXT_COLUMN, MAP_BASE_SCALE, TEXT_DEFAULT_EXPRESSION,
                    TEXT_DEFAULT_ATTRIBUTES) =
   (SELECT tname, :n.table_name, :n.column_name, :n.MAP_BASE_SCALE,
                    :n.TEXT_EXPRESSION,
           :n.TEXT_ATTRIBUTES FROM DUAL)
  WHERE F_TABLE_SCHEMA =  tname AND
     F_TABLE_NAME = :o.table_name AND F_TEXT_COLUMN = :o.column_name;
  ELSE
 EXECUTE IMMEDIATE
  'SELECT user FROM dual' into tname;
   DELETE FROM SDO_ANNOTATION_TEXT_METADATA
    WHERE  F_TABLE_SCHEMA =  tname AND
     F_TABLE_NAME = :o.table_name AND F_TEXT_COLUMN = :o.column_name;
  END IF;
end;