Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\collections\collections

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\collections\collections_triggers.sql

Rem drv: <create type="triggers" pos="blackout/blackout_triggers.sql+"/>
REM
REM $Header: collections_triggers.sql 29-aug-2007.23:17:40 jsadras Exp $
REM
REM collections_triggers.sql
REM
REM Copyright (c) 2003, 2007, Oracle. All rights reserved.  
REM
REM    NAME
REM      collections_triggers.sql - Collections subSystem triggers
REM
REM    DESCRIPTION
REM
REM    NOTES
REM
REM    MODIFIED 
REM       jsadras    08/29/07 - Backport jsadras_bug-6363234 from main
REM       pmodi      04/03/07 - Backport pmodi_bug-5876137 from main
REM       jsadras    08/28/07 - Bug:6363234, disable collections on disable
REM       pmodi      03/13/07 - Bug5876137-Clear metric err on disabling
REM                             collection
REM       jsadras    09/15/05 - do not remove task from AQ 
REM       rpinnama   09/02/05 - Add a check on mgmt_coll_item_metrics to 
REM                             disable loding multiple default collections 
REM                             for the same metric 
REM       jsadras    07/06/05 - clear alerts on null/trigger on collections
REM       gsbhatia   07/01/05 - New repmgr header impl 
REM       gsbhatia   06/26/05 - New repmgr header impl 
REM       rpinnama   05/09/05 - Re-order deletes so as to avoid deadlocks 
REM       jsadras    03/18/05 - Bug:4243501, complex schedule resume 
REM       gsbhatia   02/13/05 - updating repmgr header 
REM       rpinnama   02/10/05 - 
REM       gsbhatia   02/07/05 - updating repmgr header 
REM       rpinnama   02/08/05 - Fix update trigger on mgmt_metric_thresholds
REM       ktlaw      01/11/05 - add repmgr header 
REM       jsadras    12/30/04 - condition_operato
REM       rpinnama   12/02/04 - Use object type constants
REM       rpinnama   11/22/04 - Support for transposed metrics 
REM       jsadras    10/04/04 - delete mgmt_policy_assoc_eval 
REM       rpinnama   09/24/04 - Process is_push 
REM       rpinnama   09/01/04 - Add instead of triggers 
REM       lgloyd     11/11/03 - lgloyd_bug-3216284 
REM       lgloyd     11/10/03 - brand new file for collections triggers
REM
REM

REM
REM Shadow the mgmt_metric_collections table if this is a repository
REM based collection for better performance
REM
-- Moved to em_insert_collection trigger (instead-of trigger on mgmt_metric_collections)

REM
REM The following triggers delete corresponding entries from the
REM collections table when entries are deleted from each of the tables
REM
-- Moved to em_delete_collection trigger (instead-of trigger on mgmt_metric_collections)

--
-- Triggers for mgmt_metric_thresholds
--

CREATE OR REPLACE TRIGGER em_insert_policy_info 
  INSTEAD OF INSERT ON MGMT_METRIC_THRESHOLDS FOR EACH ROW
DECLARE
  l_target_type mgmt_metrics.target_type%TYPE;
  l_metric_name mgmt_metrics.metric_name%TYPE;
  l_metric_column mgmt_metrics.metric_column%TYPE;
  l_policy_guid mgmt_policies.policy_guid%TYPE;
  l_metric_guid mgmt_metrics.metric_guid%TYPE;
  l_metric_type mgmt_metrics.metric_type%TYPE;
BEGIN

-- Add target policy assoc
  BEGIN
    EM_POLICY.add_policy_assoc(
        p_object_guid => :new.target_guid,
        p_policy_guid => :new.metric_guid,
        p_coll_name   => :new.coll_name,
        p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      NULL; -- Ignore PK violation
  END;

-- Add policy assoc cfg
  -- TODO: Use NVL(:new.key_operator, 1) for key_operator
  EM_POLICY.add_policy_assoc_cfg(
      p_object_guid => :new.target_guid,
      p_policy_guid => :new.metric_guid,
      p_coll_name   => :new.coll_name,
      p_key_value   => :new.key_value,
      p_key_operator => 0,
      p_eval_order  => :new.eval_order,
      p_fixit_job    => :new.fixit_job,
      p_condition_operator => :new.critical_operator,
      p_num_occurrences => :new.num_occurences,
      p_is_push     => :new.is_push
  );

-- Add policy assoc cfg parameter
  -- TODO: Use NVL(:new.key_operator, 1) for key_operator
  EM_POLICY.add_policy_assoc_cfg_param(
      p_object_guid    => :new.target_guid,
      p_policy_guid    => :new.metric_guid,
      p_coll_name      => :new.coll_name,
      p_key_value      => :new.key_value,
      p_key_operator   => 0,
      p_param_name     => ' ',
      p_crit_threshold => :new.critical_threshold,
      p_warn_threshold => :new.warning_threshold,
      p_info_threshold => NULL) ;

END;
/
show errors

CREATE OR REPLACE TRIGGER em_delete_policy_cfg
   INSTEAD OF DELETE ON MGMT_METRIC_THRESHOLDS FOR EACH ROW
DECLARE
  l_cfg_cnt NUMBER;
  l_assoc_cnt NUMBER;
BEGIN

-- The delete sequence should be the following to avoid deadlocks
  --   MGMT_POLICY_ASSOC
  --   MGMT_POLICY_ASSOC_CFG
  --   MGMT_POLICY_ASSOC_CFG_PARAMS

EM_SEVERITY.clear_alerts_for_key
      (p_object_guid=>:old.target_guid,
       p_policy_guid=>:old.metric_guid,
       p_cfg_coll_name=>:old.coll_name,
       p_cfg_key_value=>:old.key_value,
       p_cfg_key_operator=>:old.key_operator,
       p_clear_message =>EM_SEVERITY.G_CLR_METRIC_REM_MSG,
       p_clear_nlsid =>EM_SEVERITY.G_CLR_METRIC_REM_NLSID
      ) ;

-- If all key cfgs have been deleted, delete the association
  SELECT COUNT(1) INTO l_cfg_cnt
    FROM mgmt_policy_assoc_cfg
   WHERE object_guid = :old.target_guid
     AND policy_guid = :old.metric_guid
     AND coll_name   = :old.coll_name
     AND (NOT (object_guid = :old.target_guid AND 
               policy_guid = :old.metric_guid AND
               coll_name = :old.coll_name AND 
               key_value = :old.key_value AND
               key_operator = 0) );

IF (l_cfg_cnt = 0) THEN

EM_POLICY.remove_policy_assocs(
        p_object_guid  => :old.target_guid,
        p_policy_guid  => :old.metric_guid,
        p_coll_name    => :old.coll_name);

END IF;

-- Delete policy assoc cfg
  -- TODO: Use NVL(:old.key_operator, 1) for key_operator
  EM_POLICY.remove_policy_assoc_cfgs(
      p_object_guid  => :old.target_guid,
      p_policy_guid  => :old.metric_guid,
      p_coll_name    => :old.coll_name,
      p_key_value    => :old.key_value,
      p_key_operator => 0);

-- Delete policy assoc cfg parameter
  -- TODO: Use NVL(:old.key_operator, 1) for key_operator
  EM_POLICY.remove_policy_assoc_cfg_params(
      p_object_guid  => :old.target_guid,
      p_policy_guid  => :old.metric_guid,
      p_coll_name    => :old.coll_name,
      p_key_value    => :old.key_value,
      p_key_operator => 0,
      p_param_name   => ' ');

END;
/
show errors;

CREATE OR REPLACE TRIGGER em_update_policy_cfg
   INSTEAD OF UPDATE ON MGMT_METRIC_THRESHOLDS FOR EACH ROW
DECLARE
BEGIN
  -- Update policy assoc
  UPDATE mgmt_policy_assoc
     SET object_guid = :new.target_guid,
         policy_guid = :new.metric_guid,
         coll_name   = :new.coll_name,
         object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
         policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
   WHERE object_guid = :old.target_guid
     AND policy_guid = :old.metric_guid
     AND coll_name   = :old.coll_name;

UPDATE mgmt_policy_assoc_cfg
     SET object_guid = :new.target_guid,
         policy_guid = :new.metric_guid,
         coll_name   = :new.coll_name,
         eval_order = NVL(:new.eval_order, eval_order),
         fixit_job = NVL(:new.fixit_job, fixit_job),
         num_occurrences = NVL(:new.num_occurences, num_occurrences),
         condition_operator = NVL(:new.critical_operator, condition_operator),
         message = NVL(:new.message, message),
         message_nlsid = NVL(:new.message_nlsid, message_nlsid)
   WHERE object_guid = :old.target_guid
     AND policy_guid = :old.metric_guid
     AND coll_name = :old.coll_name
     AND key_value = :old.key_value
     AND key_operator = 0;

UPDATE mgmt_policy_assoc_cfg_params
     SET object_guid = :new.target_guid,
         policy_guid = :new.metric_guid,
         coll_name   = :new.coll_name,
         crit_threshold = :new.critical_threshold,
         warn_threshold = :new.warning_threshold,
         info_threshold = NULL
   WHERE object_guid = :old.target_guid
     AND policy_guid = :old.metric_guid
     AND coll_name = :old.coll_name
     AND key_value = :old.key_value
     AND key_operator = 0
     AND param_name = ' ';

END;
/
show errors;

--
-- Triggers on mgmt_collection_properties
-- 
CREATE OR REPLACE TRIGGER em_insert_coll_item_property 
  INSTEAD OF INSERT ON mgmt_collection_properties FOR EACH ROW
BEGIN
  -- Add coll_item property 
  EM_COLL_UTIL.add_coll_item_property(
      p_object_guid => :new.target_guid,
      p_metric_guid => :new.metric_guid,
      p_coll_name   => :new.coll_name,
      p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
      p_property_name => :new.property_name,
      p_property_value => :new.property_value);
END;
/
show errors

CREATE OR REPLACE TRIGGER em_update_coll_item_property 
  INSTEAD OF UPDATE ON mgmt_collection_properties FOR EACH ROW
BEGIN
  -- Update coll_item property 
  UPDATE mgmt_coll_item_properties
     SET object_guid = :new.target_guid, 
         metric_guid = :new.metric_guid,
	 coll_name   = :new.coll_name,
	 property_name = :new.property_name,
	 property_value = :new.property_value
   WHERE object_guid = :old.target_guid
     AND metric_guid = :old.metric_guid
     AND coll_name = :old.coll_name
     AND property_name = :old.property_name;

END;
/
show errors

CREATE OR REPLACE TRIGGER em_delete_coll_item_property 
  INSTEAD OF DELETE ON mgmt_collection_properties FOR EACH ROW
BEGIN
  IF EMDW_LOG.P_IS_DEBUG_SET THEN
    EMDW_LOG.INFO('em_delete_coll_item_property.. Deleting ' ||
       ' Target_guid = ' || :new.target_guid || 
       ' Metric guid = ' || :new.metric_guid || 
       ' Coll_name = ' || :new.coll_name ||
       ' property_name = ' || :new.property_name, 'EM_DELETE_COLL_ITEM_PROPERTY');
  END IF ;
  -- Delete coll_item property 
  EM_COLL_UTIL.delete_coll_item_property(
      p_object_guid => :old.target_guid,
      p_metric_guid => :old.metric_guid,
      p_coll_name   => :old.coll_name,
      p_property_name => :old.property_name);
END;
/
show errors

--
-- Triggers on mgmt_collections
--
CREATE OR REPLACE TRIGGER em_insert_collection
  INSTEAD OF INSERT ON mgmt_metric_collections FOR EACH ROW
DECLARE 
  l_coll_sched mgmt_coll_schedule_obj;
  l_is_enabled mgmt_collections.is_enabled%TYPE;
  l_store_metric mgmt_collections.store_metric%TYPE;
  l_schedule NUMBER;
  l_rec em_coll_util.metrics_cursor%rowtype ;
  l_coll_name mgmt_collections.coll_name%type ;
BEGIN
  -- Add collection entry
  l_coll_sched := NULL;
  IF (:new.schedule IS NULL) THEN
    -- 15 was the default schedule previously
    l_coll_sched := mgmt_coll_schedule_obj.interval_schedule(15, NULL, NULL);
    l_schedule := 15;
  ELSE
    l_coll_sched := mgmt_coll_schedule_obj.interval_schedule(:new.schedule, NULL, NULL);
    l_schedule := :new.schedule;
  END IF;

l_is_enabled := 1;
  IF ( (:new.suspended IS NOT NULL) AND (:new.suspended = 1) ) THEN
    l_is_enabled := 0;
  END IF;

l_store_metric := 1;
  IF NOT (:new.store_metric = 'Y') THEN
    l_store_metric := 0;
  END IF;

IF EMDW_LOG.P_IS_DEBUG_SET THEN
    EMDW_LOG.INFO('em_insert_collection: Inserting ' ||
       ' Target_guid = ' || :new.target_guid || 
       ' Coll_name = ' || :new.coll_name ||
       ' is_repository = ' || :new.is_repository ||
       ' store_metric = ' || :new.store_metric, 'EM_INSERT_COLLECTION');
  END IF ;

IF :new.coll_name IS NULL
  THEN
    OPEN em_coll_util.metrics_cursor(:new.target_guid,:new.metric_guid) ;
    FETCH em_coll_util.metrics_cursor INTO l_rec ;
    l_coll_name := nvl(l_rec.metric_name,' ') ;
  ELSE
    l_coll_name := :new.coll_name ;
  END IF ;

EM_COLL_UTIL.add_collection_entry(p_object_guid=>:new.target_guid,
                                    p_coll_name  =>l_coll_name,
                                    p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
                                    p_schedule_ex => :new.schedule_ex,
                                    p_coll_schedule => l_coll_sched,
                                    p_is_repository => :new.is_repository,
                                    p_store_metric  => l_store_metric,
                                    p_is_enabled   => l_is_enabled);

EM_COLL_UTIL.add_modify_metric_task(p_target_guid=>:new.target_guid,
                                      p_metric_guid=>:new.metric_guid,
                                      p_coll_name=>l_coll_name,
                                      p_task_id=>null,
                                      p_dml_operation=>'I',
                                      p_from_trigger=>TRUE) ;
 
  IF :new.is_repository =  MGMT_GLOBAL.G_TRUE
  THEN
    --Table to obsoleted  in next phase
    INSERT INTO MGMT_METRIC_COLLECTIONS_REP
      (target_guid, metric_guid, coll_name) 
     VALUES
      (:new.target_guid, :new.metric_guid, l_coll_name);
     -- Schedule a repository collection if it is not suspended
     IF nvl(:new.suspended,0) != 1
     THEN
       IF NOT em_coll_util.metrics_cursor%ISOPEN
       THEN
         OPEN em_coll_util.metrics_cursor(:new.target_guid,:new.metric_guid) ;
         FETCH em_coll_util.metrics_cursor INTO l_rec ;
       END IF ;
       IF l_rec.metric_type IN ( MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE,
                                 MGMT_GLOBAL.G_METRIC_TYPE_REPOS_NUMBER,
                                 MGMT_GLOBAL.G_METRIC_TYPE_REPOS_STRING) AND
          l_rec.cat_props IS NOT NULL AND
          l_rec.timezone_region IS NOT NULL
       THEN
         EM_COLL_UTIL.schedule_repo_collection
                      (p_target_guid=>:new.target_guid,
                       p_metric_guid=>:new.metric_guid,
                       p_coll_name=>l_coll_name,
                       p_op_code=>EM_COLL_UTIL.G_SCHEDULE_COLLECTION_OP,
                       p_source_type=> MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL,
                       p_coll_schedule=>l_coll_sched,
                       p_cat_prop_array=>l_rec.cat_props,
                       p_timezone_region=>l_rec.timezone_region) ;
         IF nvl(l_rec.is_repository,0) != MGMT_GLOBAL.G_TRUE
         THEN
           -- If metric is not set as repository set it now
           UPDATE mgmt_metrics
              SET is_repository = MGMT_GLOBAL.G_TRUE
            WHERE ROWID =  l_rec.metrics_rowid ;
         END IF ;
       END IF ;
     END IF ; -- not suspended
  END IF;

IF em_coll_util.metrics_cursor%ISOPEN
  THEN
    CLOSE em_coll_util.metrics_cursor ;
  END IF ;
END;
/
show errors

CREATE OR REPLACE TRIGGER em_update_collection
  INSTEAD OF UPDATE ON mgmt_metric_collections FOR EACH ROW
DECLARE
  l_coll_sched mgmt_coll_schedule_obj;
  l_is_enabled mgmt_collections.is_enabled%TYPE;
  l_store_metric mgmt_collections.store_metric%TYPE;
  l_op_code NUMBER ;
  l_rec em_coll_util.metrics_cursor%rowtype ;
  l_target_name mgmt_targets.target_name%type ;
  l_target_type mgmt_targets.target_type%type ;
BEGIN
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('em_update_collection:Enter '||
      ' Target='||:new.target_guid||
      ' Coll='||:new.coll_name,MGMT_COLLECTION.G_MODULE_NAME);
    EMDW_LOG.DEBUG('em_update_collection:New values='||
      ' suspended='||:new.suspended||
      ' store_metric='||:new.store_metric||
      ' schedule='||:new.schedule||
      ' schedule_ex='||:new.schedule_ex,MGMT_COLLECTION.G_MODULE_NAME) ;
  END IF ;
  -- Add coll_item property 
  l_coll_sched := NULL;
  IF (:new.schedule IS NOT NULL) THEN
    l_coll_sched := mgmt_coll_schedule_obj.interval_schedule(:new.schedule, NULL, NULL);
  END IF;

l_is_enabled := 1;
  IF ( (:new.suspended IS NOT NULL) AND (:new.suspended = 1) ) THEN
    l_is_enabled := 0;
  END IF;

l_store_metric := 1;
  IF NOT (:new.store_metric = 'Y') THEN
    l_store_metric := 0;
  END IF;

IF l_coll_sched IS NULL THEN
    UPDATE mgmt_collections
       SET object_guid = :new.target_guid,
           object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
           coll_name   = :new.coll_name,
           is_enabled = nvl(l_is_enabled,is_enabled),
           store_metric = nvl(l_store_metric,store_metric),
           schedule_ex = nvl(:new.schedule_ex,schedule_ex)
     WHERE object_guid = :old.target_guid
       AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
       AND coll_name   = :old.coll_name  ;
  ELSE
    UPDATE mgmt_collections
       SET object_guid = :new.target_guid,
           object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
           coll_name   = :new.coll_name,
           is_enabled = nvl(l_is_enabled,is_enabled),
           store_metric = nvl(l_store_metric,store_metric),
           frequency_code=l_coll_sched.frequency_code,
           start_time=l_coll_sched.start_time ,
           end_time=l_coll_sched.end_time,
           execution_hours=l_coll_sched.execution_hours,
           execution_minutes=l_coll_sched.execution_minutes ,
           interval=l_coll_sched.interval ,
           days=l_coll_sched.days ,
           months=l_coll_sched.months ,
           schedule_ex = nvl(:new.schedule_ex,schedule_ex)
     WHERE object_guid = :old.target_guid
       AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET
       AND coll_name   = :old.coll_name  ;
  END IF;

-- Update last_collected_timestamp, status message
  UPDATE mgmt_collection_metric_tasks
     SET target_guid = :new.target_guid,
         metric_guid = :new.metric_guid,
         coll_name = :new.coll_name,
         last_collected_timestamp = :new.last_collected_timestamp,
         status_message = :new.status_message
   WHERE target_guid = :old.target_guid
     AND metric_guid = :old.metric_guid
     AND coll_name = :old.coll_name;

IF (:new.suspended = 0 AND :old.suspended= 1) OR
     ( :new.schedule > 0 AND (:new.schedule != :old.schedule))
  THEN
    l_op_code := EM_COLL_UTIL.G_SCHEDULE_COLLECTION_OP ;
  ELSIF (:new.suspended = 1 AND :old.suspended = 0)
  THEN
    l_op_code := EM_COLL_UTIL.G_SUSPEND_COLLECTION_OP ;
  END IF ;

IF l_op_code IS NOT NULL
  THEN
    OPEN em_coll_util.metrics_cursor(:new.target_guid,:new.metric_guid) ;
    FETCH em_coll_util.metrics_cursor INTO l_rec ;
    CLOSE em_coll_util.metrics_cursor ;
    IF l_rec.is_repository = MGMT_GLOBAL.G_TRUE
    THEN
      SELECT target_name,target_type
        INTO l_target_name,l_target_type
        FROM mgmt_targets
       WHERE target_guid = :NEW.target_guid ;
      
      EM_COLL_UTIL.suspend_resume_stop_collection
         (p_target_name=>l_target_name,
          p_target_type=>l_target_type,
          p_coll_name=>:old.coll_name,
          p_op_code=>l_op_code,
          p_target_guid=>:NEW.target_guid) ;
    END IF ;
  END IF ;
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('em_update_collection:Exit',MGMT_COLLECTION.G_MODULE_NAME);
  END IF ;
END;
/
show errors

CREATE OR REPLACE TRIGGER em_delete_collection
  INSTEAD OF DELETE ON mgmt_metric_collections FOR EACH ROW
DECLARE
  l_metric_cnt NUMBER;
  l_task_id mgmt_collection_tasks.task_id%type ;
BEGIN

DELETE FROM MGMT_METRIC_COLLECTIONS_REP 
   WHERE target_guid=:old.target_guid
     AND metric_guid=:old.metric_guid 
     AND coll_name=:old.coll_name;
  BEGIN
    SELECT task_id
      INTO l_task_id
      FROM mgmt_collection_metric_tasks
     WHERE target_guid = :old.target_guid AND
           metric_guid = :old.metric_guid AND
           coll_name   = :old.coll_name ;

EM_COLL_UTIL.add_modify_metric_task(p_target_guid=>:old.target_guid,
                                        p_metric_guid=>:old.metric_guid,
                                        p_coll_name=>:old.coll_name,
                                        p_dml_operation=>'D',
                                        p_from_trigger=>TRUE) ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL ;
  END ;

SELECT COUNT(1) INTO l_metric_cnt
    FROM mgmt_collection_metric_tasks
   WHERE target_guid = :old.target_guid
     AND coll_name = :old.coll_name;

IF (l_metric_cnt <= 0) THEN
    EM_COLL_UTIL.delete_collection_entry(
        p_object_guid => :old.target_guid,
	p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET,
	p_coll_name   => :old.coll_name,
        p_from_trigger => TRUE);
  END IF;

END;
/
show errors

-- If collection is disabled, during insert or update then execute run_disable_steps
CREATE OR REPLACE TRIGGER collection_disable_trigger
   BEFORE INSERT OR UPDATE ON MGMT_COLLECTIONS FOR EACH ROW
WHEN ( new.is_enabled  = 0 ) 
DECLARE
  l_tbl_metric_guid MGMT_METRICS.metric_guid%TYPE;
BEGIN
  IF ( (:new.object_type =  MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) AND
       (INSERTING OR (UPDATING AND :old.is_enabled = 1) ) )
  THEN
    EM_COLL_UTIL.run_disable_steps(:new.object_guid,:new.coll_name) ;
  END IF;
END collection_disable_trigger ;
/
show errors

CREATE OR REPLACE TRIGGER coll_items_ins_trig
   BEFORE INSERT ON MGMT_COLL_ITEMS FOR EACH ROW
DECLARE
  l_coll_cnt NUMBER := 0;
BEGIN
  -- Check to see if default collection is created with proper cat props
  -- Check to see if there are any valid-if overlaps
  SELECT COUNT(*) INTO l_coll_cnt FROM MGMT_COLL_ITEMS
   WHERE target_type = :new.target_type
     AND type_meta_ver = :new.type_meta_ver
     AND coll_name = :new.coll_name
     AND (    (category_prop_1 = :new.category_prop_1)
           OR (:new.category_prop_1 = ' ')
           OR (category_prop_1 = ' ') )
     AND (    (category_prop_2 = :new.category_prop_2)
           OR (:new.category_prop_2 = ' ')
           OR (category_prop_2 = ' ') )
     AND (    (category_prop_3 = :new.category_prop_3)
           OR (:new.category_prop_3 = ' ')
           OR (category_prop_3 = ' ') )
     AND (    (category_prop_4 = :new.category_prop_4)
           OR (:new.category_prop_4 = ' ')
           OR (category_prop_4 = ' ') )
     AND (    (category_prop_5 = :new.category_prop_5)
           OR (:new.category_prop_5 = ' ')
           OR (category_prop_5 = ' ') )
     AND (NOT ( (category_prop_1 = :new.category_prop_1) AND
                (category_prop_2 = :new.category_prop_2) AND
                (category_prop_3 = :new.category_prop_3) AND
                (category_prop_4 = :new.category_prop_4) AND
                (category_prop_5 = :new.category_prop_5) ) ) ;

IF (EMDW_LOG.P_IS_DEBUG_SET)THEN
     EMDW_LOG.DEBUG('coll_items_insert_trigger: Colls overlapping cnt = ' || l_coll_cnt,
       MGMT_COLLECTION.G_MODULE_NAME);
  END IF;

IF (l_coll_cnt > 0) THEN
     raise_application_error(MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR,
         MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR_M ||
         ' target type = [' || :new.target_type || ']' ||
         ' type ver = [' || :new.type_meta_ver || ']' || 
         ' coll name = [' || :new.coll_name || ']' ||
         ' cat prop1 = [' || :new.category_prop_1 || ']' ||
         ' cat prop2 = [' || :new.category_prop_2 || ']' ||
         ' cat prop3 = [' || :new.category_prop_3 || ']' ||
         ' cat prop4 = [' || :new.category_prop_4 || ']' ||
         ' cat prop5 = [' || :new.category_prop_5 || ']' );
  END IF;

END;
/
show errors

CREATE OR REPLACE TRIGGER coll_items_metrics_ins_trig
   BEFORE INSERT ON MGMT_COLL_ITEM_METRICS FOR EACH ROW
DECLARE
  l_coll_cnt NUMBER := 0;
BEGIN

-- Check to see if the metric has duplicate default collections

-- Get all cat prop combinations for the given coll_name
  FOR cp_rec IN (SELECT target_type, type_meta_ver, coll_name,
                        category_prop_1, category_prop_2, category_prop_3,
                        category_prop_4, category_prop_5
                   FROM mgmt_coll_items
                  WHERE target_type = :new.target_type
                    AND type_meta_ver = :new.type_meta_ver
                    AND coll_name = :new.coll_name)
  LOOP
    -- Check to see if there are any overlaps with the cat prop list

-- For the same target_type, type_meta_ver, metric, check to see
    -- if there is any other default collection
    SELECT COUNT(*) INTO l_coll_cnt
      FROM mgmt_coll_items ci, mgmt_coll_item_metrics cim
     WHERE ci.target_type = cim.target_type
       AND ci.type_meta_ver = cim.type_meta_ver
       AND ci.coll_name = cim.coll_name
       AND ci.target_type = cp_rec.target_type
       AND ci.type_meta_ver = cp_rec.type_meta_ver
       AND cim.metric_guid = :new.metric_guid
       AND (   (ci.category_prop_1 = cp_rec.category_prop_1)
            OR (ci.category_prop_1 = ' ')
            OR (cp_rec.category_prop_1 = ' ') )
       AND (   (ci.category_prop_2 = cp_rec.category_prop_2)
            OR (ci.category_prop_2 = ' ')
            OR (cp_rec.category_prop_2 = ' ') )
       AND (   (ci.category_prop_3 = cp_rec.category_prop_3)
            OR (ci.category_prop_3 = ' ')
            OR (cp_rec.category_prop_3 = ' ') )
       AND (   (ci.category_prop_4 = cp_rec.category_prop_4)
            OR (ci.category_prop_4 = ' ')
            OR (cp_rec.category_prop_4 = ' ') )
       AND (   (ci.category_prop_5 = cp_rec.category_prop_5)
            OR (ci.category_prop_5 = ' ')
            OR (cp_rec.category_prop_5 = ' ') )
       AND ci.coll_name <> cp_rec.coll_name;

IF (EMDW_LOG.P_IS_DEBUG_SET)THEN
       EMDW_LOG.DEBUG('coll_item_metrics_insert_trigger: Colls overlapping cnt = ' || l_coll_cnt,
         MGMT_COLLECTION.G_MODULE_NAME);
    END IF;

IF (l_coll_cnt > 0) THEN
       raise_application_error(MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR,
         MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR_M ||
         ' target type = [' || :new.target_type || ']' ||
         ' type ver = [' || :new.type_meta_ver || ']' ||
         ' coll name = [' || :new.coll_name || ']' ||
         ' metric_guid = [' || :new.metric_guid || ']' ||
         ' cat prop1 = [' || cp_rec.category_prop_1 || ']' ||
         ' cat prop2 = [' || cp_rec.category_prop_2 || ']' ||
         ' cat prop3 = [' || cp_rec.category_prop_3 || ']' ||
         ' cat prop4 = [' || cp_rec.category_prop_4 || ']' ||
         ' cat prop5 = [' || cp_rec.category_prop_5 || ']' );
    END IF;

END LOOP;

END;
/
show errors

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de