TRIGGER: APPS.MSC_APPS_INSTANCES_T2

Source

Description

"APPS"."MSC_APPS_INSTANCES_T2" 
BEFORE UPDATE OF instance_type
 ON  "MSC"."MSC_APPS_INSTANCES#"  FOR EACH ROW

Type

BEFORE EACH ROW

Event

UPDATE

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   lv_sql_stmt VARCHAR2(500);
BEGIN
   IF :old.instance_type = 3 AND :new.instance_type <> 3 THEN
      lv_sql_stmt := 'BEGIN MSC_CL_EXCHANGE_PARTTBL.modify_st_partition_drop (:instance_id); END;';
      EXECUTE IMMEDIATE lv_sql_stmt USING :old.instance_id;

      lv_sql_stmt := 'BEGIN MSC_CL_EXCHANGE_PARTTBL.create_st_partition (:instance_id); END;';
      EXECUTE IMMEDIATE lv_sql_stmt USING :new.instance_id;
   END IF;

   IF :old.instance_type <> 3 and :new.instance_type = 3 THEN
      lv_sql_stmt := 'BEGIN MSC_CL_EXCHANGE_PARTTBL.drop_st_partition (:instance_id); END;';
      EXECUTE IMMEDIATE lv_sql_stmt USING :old.instance_id;

      lv_sql_stmt := 'BEGIN MSC_CL_EXCHANGE_PARTTBL.modify_st_partition_add (:instance_id); END;';
      EXECUTE IMMEDIATE lv_sql_stmt USING :new.instance_id;
   END IF;

END;