Rem drv: Rem Rem $Header: basic_triggers.sql 18-aug-2007.09:58:12 denath Exp $ Rem Rem basic_triggers.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem basic_triggers.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem denath 07/31/07 - Fix 6322920.changed metrics_insert_trigger for Rem is_transposed. Rem denath 08/18/07 - Backport denath_bug-6322920 from main Rem joyoon 07/25/07 - Backport joyoon_bug-5647087 from main Rem jsadras 07/21/07 - Fix bug:5092508, timezone equivalence Rem jsadras 07/30/07 - Backport jsadras_bug-5092508 from main Rem scgrover 07/12/06 - Backport scgrover_bug-5043773 from main Rem rkpandey 09/09/05 - Bug 4580359: Check TZ region for multi_agent Rem target Rem rpinnama 09/02/05 - Enhance the check to detect overlapping metric Rem definitions Rem pmodi 08/24/05 - Bug:4561906 use is_fatally_broken function Rem rkpandey 08/25/05 - Avoid PK violations in metrics insert Rem jsadras 08/17/05 - add catprops to mgmt_target_meta_ver_cbk_obj Rem gan 08/03/05 - set default avail record if R/S metric is Rem defined Rem pmodi 07/20/05 - Bug:4260179 - Start metric error for broken Rem target Rem pmodi 07/15/05 - Bug:4488153 Handle NULL TZR in update trigger Rem on mgmt_targets Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem pmodi 06/14/05 - Bug:4408959 -Warning is not an error start Rem pmodi 06/23/05 - Bug:4307553 - Availability is not applicable Rem for system type target Rem snakai 06/14/05 - do not call gensvc avail on metric err warns Rem neearora 06/22/05 - Bug 4358570. throws AGENT_DELETION_IN_PROG_ERR exception Rem from before insert trigger on mgmt_target if agent traget. Rem is under deletion. Rem Throw stale_target_addtion_exception exception from before Rem insert trigger on mgmt_target if upload_time is < delete_complete_time Rem neearora 06/10/05 - fixed bug 4391160 Rem jsadras 05/19/05 - target properties Rem rpinnama 05/12/05 - grabtrans 'rpinnama_bug-4318176' Rem pmodi 05/26/05 - Bug:4396320- set is_transposed to 1 for host/UDM Rem and oracle_database/SQLUDM Rem rpinnama 05/08/05 - Bug 4302515: Support change agent url req and Rem targets rowset in different files Rem rpinnama 04/27/05 - Donot raise UPDATE_TZ error for targets that Rem have no availability history Rem rpinnama 04/21/05 - Fix 4247517 : Detect invalid metrics and raise Rem overlapping metric error Rem rpinnama 04/22/05 - Fix 4295266 : Do not insert NULL current metrics Rem on errors Rem streddy 04/10/05 - Propagate is_aggregate for cluster types Rem neearora 03/24/05 - Added check fortarget type meta version in before Rem insert/update trigger on mgmt_targets Rem jsadras 03/10/05 - meta ver changes Rem rpinnama 03/15/05 - Fix 4116241: Throw exception, if a timezone Rem region change is detected Rem ramalhot 03/03/05 - changed implementation of MGMT_CHANGE_AGENT_URL Rem rpinnama 02/25/05 - Bug 3924067: Add non_thresholded_alerts and Rem key_only_thresholds columns Rem pmodi 02/23/05 - If timezone_region is NULL then use repo tzr Rem in targets_insert_trigger Rem ramalhot 02/23/05 - call to init_metric_severity_deps removed Rem gan 02/17/05 - auto add is_aggregate for agg svc Rem asawant 02/08/05 - Remove insert trigger for mgmt_target_properties Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem rpinnama 02/08/05 - Add threshold policies even for key columns Rem rzazueta 01/12/05 - Remove call to Rem MGMT_BLACKOUT_ENGINE.handle_membership_change Rem ktlaw 01/11/05 - add repmgr header Rem rpinnama 01/05/05 - Remove the trigger on mgmt_target_type_versions Rem asawant 11/10/04 - Move property propagation code. Rem rpinnama 12/09/04 - Add a metric threshold policy for all non-key Rem columns Rem rpinnama 12/03/04 - Fix 4044204 : Add procedure to add host Rem Respnse/Status metric Rem rpinnama 11/29/04 - Add delete trigger on mgmt_targets to disable Rem deletes Rem snakai 11/22/04 - update beacon avail calls Rem streddy 11/04/04 - Add is_aggregate property Rem aholser 09/24/04 - Prevent version regress for emrep target Rem jsadras 09/21/04 - eval_func Rem rkpandey 09/15/04 - Default value for timezone added in insert Rem trigger Rem rpinnama 09/13/04 - mgmt_severity -> mgmt_violations Rem ramalhot 08/31/04 - cutover to new assoc tables Rem asawant 06/04/04 - Adding trigger code necessary for UDTP Rem asawant 07/14/04 - Adding trigger on mgmt_target_properties Rem gan 07/01/04 - target direct load Rem rpinnama 06/28/04 - Use generic Response/Status metric for host Rem streddy 12/10/03 - Check for NULL monitoring_mode values Rem skini 12/07/03 - Rem skini 12/05/03 - Add an entry in MGMT_BLACKOUT_PROXY_TARGETS Rem rzazueta 11/11/03 - Fix bug 3217620: Fix mutating ex. in Rem mgmt_metric_errors trigger Rem rzazueta 11/09/03 - Fix bug 3159465: Update mgmt_metrics trigger Rem streddy 10/15/03 - Update flat groups for composite targets case Rem rpinnama 10/15/03 - Add triger for target_property_defs Rem streddy 09/26/03 - Don't allow modifying monitoring_mode Rem streddy 09/25/03 - Added type_meta_ver for rep metrics Rem rzazueta 09/11/03 - Fix bug 2616562 Rem rzazueta 09/05/03 - Fix bug 3091097: Update MGMT_METRICS insert trigger Rem rpinnama 09/06/03 - Call add_target callback for purge subsystem Rem snakai 08/01/03 - get more detailed metric error msg if it is a beacon avail target Rem streddy 08/04/03 - Comment out group_history mgmt Rem rpinnama 07/18/03 - Support agent_guid, coll_name in the metric_error trigger Rem streddy 04/21/03 - Target added callback and master-agent support Rem rpinnama 05/13/03 - Add metric versioning support for host Response/Status metric Rem aholser 04/08/03 - allow new emd_url for oracle_emrep target type Rem streddy 03/18/03 - Add after delete on MGMT_METRIC_DEPENDENCY table Rem sgrover 03/19/03 - remove load_timestamp Rem ancheng 02/14/03 - target version fix Rem vnukal 12/30/02 - Add coll_name to mgmt_metric_errors and mgmt_current_metric_errors Rem rpinnama 01/13/03 - Catch multiple oracle_emrep (repository) targets Rem rpinnama 12/18/02 - Remove check for delete targets while adding targets Rem rpinnama 11/25/02 - Ensure that there is only one agent target per emd_url Rem skini 11/26/02 - Check for blackout membership Rem rpinnama 11/13/02 - remove empty trigger Rem rpinnama 11/12/02 - Rem rpatti 10/31/02 - no grp availability Rem rpinnama 11/08/02 - Add after insert on MGMT_CHANGE_AGENT_URL table Rem rpinnama 10/16/02 - Fix the category clause Rem rpinnama 10/11/02 - Fix the 1427 error in get_metric_columns cursor Rem rpinnama 10/10/02 - Fix bug 2584591: Dont clear metric with Rem keys_from_mult_colls on metric errors. Rem rpinnama 10/10/02 - Check for last_string_value to be NULL Rem rpinnama 10/09/02 - Support metric versioning and Rem improve the performance of mgmt_metrics_raw trigger Rem rpinnama 09/23/02 - Fix metric error trigger Rem rpinnama 09/12/02 - Add syc time stamp and emd uptiems Rem edemembe 08/22/02 - Adding IOTs Rem snakai 07/25/02 - beacon funcs/procs moved to pkg Rem rpinnama 07/31/02 - Fix PK violation while clearing current_metrics Rem rpinnama 07/25/02 - Use API to create host/Response/Status metric Rem rpinnama 07/23/02 - throw appropriate exceptions Rem rpinnama 07/17/02 - restructure beacon availability calculation Rem snakai 07/12/02 - add call to beacon avail on error sev Rem rpinnama 07/08/02 - Pass metric error message to severity Rem rpinnama 07/05/02 - Rem rpinnama 07/05/02 - Use Response / Status metric guid while generatic metric error start sevs Rem skini 07/02/02 - Account for delete-pending targets Rem rpinnama 06/12/02 - truncate the bootstrap record. Rem rpinnama 06/05/02 - Modify error trigger to insert severity record. Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem If the label is null, then default it to the metric name. The same goes rem for the short_name. If it is null, default to the first 12 chars of the rem label. rem CREATE OR REPLACE TRIGGER metrics_insert_trigger BEFORE INSERT ON MGMT_METRICS FOR EACH ROW DECLARE l_tbl_metric_guid MGMT_METRICS.metric_guid%TYPE; l_alertable mgmt_metrics_ext.alertable%TYPE; l_thresholdable mgmt_metrics_ext.thresholdable%TYPE; l_keyonly_thresholds mgmt_metrics_ext.keyonly_thresholds%TYPE; l_met_cnt NUMBER := 0; BEGIN IF (EMDW_LOG.P_IS_DEBUG_SET) THEN EMDW_LOG.DEBUG('metrics_insert_trigger: Entry ' || ' target type = [' || :new.target_type || ']' || ' metric_name = [' || :new.metric_name || ']' || ' metric_column = [' || :new.metric_column || ']' || ' type ver = [' || :new.type_meta_ver || ']' || ' c prop1 = [' || :new.category_prop_1 || ']' || ' c prop2 = [' || :new.category_prop_2 || ']' || ' c prop3 = [' || :new.category_prop_3 || ']' || ' c prop4 = [' || :new.category_prop_4 || ']' || ' c prop5 = [' || :new.category_prop_5 || ']', EM_METRIC.G_MODULE_NAME); END IF; IF :new.metric_label IS NULL THEN :new.metric_label := :new.metric_name; END IF; IF :new.column_label IS NULL THEN :new.column_label := :new.metric_column; END IF; IF :new.short_name IS NULL THEN -- is this a metric definition row? IF :new.metric_column = ' ' THEN :new.short_name := SUBSTR(:new.metric_label,1,14); ELSE :new.short_name := SUBSTR(:new.column_label,1,14); END IF; END IF; IF :new.eval_func IS NOT NULL AND :new.source IS NULL THEN :new.source_type := MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL ; :new.source := :new.eval_func ; END IF; -- Check to see if this metric is created with proper categorty properties -- Check to see if there are any valid-if overlaps SELECT COUNT(*) INTO l_met_cnt FROM MGMT_METRICS WHERE target_type = :new.target_type AND metric_name = :new.metric_name AND metric_column = :new.metric_column AND type_meta_ver = :new.type_meta_ver 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('metrics_insert_trigger: Metrics overlapping cnt = ' || l_met_cnt, EM_METRIC.G_MODULE_NAME); END IF; IF (l_met_cnt > 0) THEN raise_application_error(MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR, MGMT_GLOBAL.OVERLAPPING_CATPROP_DEF_ERR_M || ' target type = [' || :new.target_type || ']' || ' metric name = [' || :new.metric_name || ']' || ' metric column = [' || :new.metric_column || ']' || ' type ver = [' || :new.type_meta_ver || ']' || ' 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; -- NOTE: This is done to eliminate the mgmt_metrics_ext table IF (:new.non_thresholded_alerts IS NULL OR :new.keyonly_thresholds IS NULL) THEN BEGIN SELECT alertable, thresholdable, keyonly_thresholds INTO l_alertable, l_thresholdable, l_keyonly_thresholds FROM mgmt_metrics_ext WHERE metric_guid = :new.metric_guid; IF (l_alertable = 'Y' AND l_thresholdable = 'N') THEN :new.non_thresholded_alerts := 1; ELSE :new.non_thresholded_alerts := 0; END IF; IF (l_keyonly_thresholds = 'Y') THEN :new.keyonly_thresholds := 1; ELSE :new.keyonly_thresholds := 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- Default to 0, if the entry is not found in mgmt_metrics_ext table :new.non_thresholded_alerts := 0; :new.keyonly_thresholds := 0; END; END IF; -- Add a threshold policy for non-key metric columns IF (:new.metric_column <> ' ') THEN -- Get table metric guid BEGIN SELECT DISTINCT metric_guid INTO l_tbl_metric_guid FROM MGMT_METRICS WHERE target_type = :new.target_type AND metric_name = :new.metric_name AND metric_column = ' '; EXCEPTION WHEN NO_DATA_FOUND THEN l_tbl_metric_guid := MGMT_METRIC.generate_metric_guid(:new.target_type, :new.metric_name); END; BEGIN EM_POLICY.add_policy( p_policy_guid => :new.metric_guid, p_target_type => :new.target_type, p_policy_name => :new.metric_name || ' ' || :new.metric_column, p_metric_guid => l_tbl_metric_guid, p_policy_type => MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, p_condition_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_condition => :new.metric_column); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicates NULL; END; END IF; IF (EMDW_LOG.P_IS_DEBUG_SET)THEN EMDW_LOG.DEBUG('metrics_insert_trigger: Exit ' || ' target type = [' || :new.target_type || ']' || ' metric_name = [' || :new.metric_name || ']' || ' metric_column = [' || :new.metric_column || ']' || ' type ver = [' || :new.type_meta_ver || ']', EM_METRIC.G_MODULE_NAME); END IF; -- set remote metrics if it is not set. Check only when -- the value is NULL IF (:new.remote IS NULL ) THEN --Join with the test meta data tables to check -- if the metric is a test metric IF ( ( ( :new.target_type = MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE ) OR ( :new.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE ) ) AND ( (:new.metric_name = 'http_response' ) OR ( :new.metric_name = 'http_content' ) OR ( :new.metric_name = 'ping' ) OR ( :new.metric_name = 'traceroute' ) ) ) THEN :new.remote := 1; ELSE :new.remote := 0; END IF; END IF; -- Set is_transposed to 1 for target_type host and metric_name UDM -- Set is_transposed to 1 for target_type oracle_database and metric_name SQLUDM, SQLUDMNUM, SQLUDMSTR -- For all other case set it to 0 IF ((:new.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND :new.metric_name = 'UDM') OR (:new.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND :new.metric_name IN ('SQLUDM','SQLUDMNUM','SQLUDMSTR'))) THEN :new.is_transposed := 1; ELSE IF (:new.is_transposed IS NULL) THEN :new.is_transposed := 0; END IF; END IF; END; / show errors; CREATE OR REPLACE TRIGGER metrics_update_trigger BEFORE UPDATE ON MGMT_METRICS FOR EACH ROW BEGIN IF ((:new.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND :new.metric_name = 'UDM') OR (:new.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND :new.metric_name IN ('SQLUDM', 'SQLUDMSTR', 'SQLUDMNUM'))) THEN :new.is_transposed := 1; ELSE :new.is_transposed := :old.is_transposed; END IF; END; / show errors; rem rem If a metric is removed, we also want to delete the data for the metric rem rem This trigger is broken as deleting a metric flavour would delete rem data for all metric flavours -- CREATE OR REPLACE TRIGGER metrics_delete -- AFTER DELETE ON MGMT_METRICS FOR EACH ROW -- BEGIN -- DELETE FROM MGMT_METRICS_1DAY r -- WHERE r.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_SEVERITY s -- WHERE s.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_METRICS_1HOUR r -- WHERE r.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_METRICS_RAW r -- WHERE r.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_CURRENT_METRICS r -- WHERE r.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_STRING_METRIC_HISTORY r -- WHERE r.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_METRIC_THRESHOLDS t -- WHERE t.metric_guid = :old.metric_guid; -- -- DELETE FROM MGMT_METRIC_COLLECTIONS c -- WHERE c.metric_guid = :old.metric_guid; -- -- No index, bad performance. Needs to be added back in with improved -- delete strategy. -- -- DELETE FROM MGMT_RT_METRICS_RAW r -- WHERE r.target_name = :old.target_name -- AND r.target_type = :old.target_type -- AND r.metric_name = :old.metric_name; -- END; -- / -- show errors; rem rem Triggers on MGMT_TARGET_PROP_DEFS rem rem If property display name is null, default to property name rem CREATE OR REPLACE TRIGGER target_prop_defs_tr BEFORE INSERT OR UPDATE ON MGMT_TARGET_PROP_DEFS FOR EACH ROW BEGIN IF :new.property_display_name IS NULL THEN :new.property_display_name := :new.property_name; END IF; END; / rem rem Triggers on MGMT_TYPE_PROPERTIES rem rem This trigger adds is_aggregate property for rem group and composite types. rem CREATE OR REPLACE TRIGGER type_properties_tr BEFORE INSERT ON MGMT_TYPE_PROPERTIES FOR EACH ROW BEGIN IF ((:new.property_name = mgmt_global.G_IS_GROUP_PROP OR :new.property_name = mgmt_global.G_IS_COMPOSITE_PROP OR :new.property_name = mgmt_global.G_IS_CLUSTER_PROP OR :new.property_name = mgmt_global.G_IS_AGGREGATE_SERVICE_PROP) AND :new.property_value = '1') THEN MGMT_TARGET.add_target_type_property(:new.target_type, MGMT_GLOBAL.G_IS_AGGREGATE_PROP, '1'); END IF; END; / rem rem Triggers on MGMT_ALL_TARGET_PROPS rem rem If property display name is null, default to property name rem CREATE OR REPLACE TRIGGER all_target_props_tr BEFORE INSERT OR UPDATE ON MGMT_ALL_TARGET_PROPS FOR EACH ROW BEGIN IF :new.property_display_name IS NULL THEN :new.property_display_name := :new.property_name; END IF; END; / rem TRIGGERS ON MGMT_TARGETS rem rem Check if there were any members defined for this target rem and add the guid information as necessary. rem CREATE OR REPLACE TRIGGER targets_insert_trigger BEFORE INSERT ON MGMT_TARGETS FOR EACH ROW DECLARE l_del_tgt_cnt NUMBER; l_agent_cnt NUMBER; l_rep_cnt NUMBER; l_agent_name_cnt NUMBER; l_host_guid MGMT_TARGETS.target_guid%TYPE; l_rs_defined BOOLEAN; l_meta_ver VARCHAR2(8); l_old_ver NUMBER; l_new_ver NUMBER; l_del_comp_time MGMT_TARGETS_DELETE.delete_complete_time%TYPE; l_old_timezone_region MGMT_TARGETS.timezone_region%TYPE; BEGIN BEGIN -- Begin for the Global exception block to catch PK violations -- Check if the target is being deleted. -- A target is being deleted if a row exists in mgmt_targets_delete -- with delete_complete_time as NULL SELECT COUNT(*), MAX(delete_complete_time) INTO l_del_tgt_cnt, l_del_comp_time FROM MGMT_TARGETS_DELETE WHERE target_guid=:new.target_guid; IF l_del_tgt_cnt > 0 THEN IF l_del_comp_time IS NULL THEN -- if the target that is being deleted is agent the throw agent_deletion_in_prog EXCEPTION IF (:new.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE) THEN raise_application_error(MGMT_GLOBAL.AGENT_DELETION_IN_PROG_ERR, MGMT_GLOBAL.AGENT_DELETION_IN_PROG_ERR_M || '(agent name = ' || :new.target_name || ')' || '(agent guid = ' || :new.target_guid || ')'); END IF; raise_application_error(MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR, MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR_M || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(target guid = ' || :new.target_guid || ')'); ELSE -- IF(EMD_LOADER.p_current_upload_time IS NOT NULL AND EMD_LOADER.p_current_upload_time <= TO_CHAR(l_del_comp_time,'YYYY-MM-DD HH24:MI:SS')) THEN raise_application_error(MGMT_GLOBAL.STALE_TARGET_ADDITION_ERR, MGMT_GLOBAL.STALE_TARGET_ADDITION_ERR_M || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(target guid = ' || :new.target_guid || ')'); END IF; END IF; END IF; -- Check if there is already another agent target for the emd_url IF (:new.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE) THEN SELECT COUNT(*) INTO l_agent_cnt FROM MGMT_TARGETS WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND emd_url = :new.emd_url AND NOT (target_name = :new.target_name); IF (l_agent_cnt > 0) THEN raise_application_error(MGMT_GLOBAL.MULTIPLE_AGENTS_PER_URL_ERR, MGMT_GLOBAL.MULTIPLE_AGENTS_PER_URL_ERR_M || '(agent URL = ' || :new.emd_url || ')' ); END IF; ELSIF (:new.target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE) THEN -- Check if there is a different repository target SELECT COUNT(*) INTO l_rep_cnt FROM MGMT_TARGETS WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND NOT (target_name = :new.target_name); IF (l_rep_cnt > 0) THEN raise_application_error(MGMT_GLOBAL.DUPLICATE_TARGET_ERR, MGMT_GLOBAL.DUPLICATE_TARGET_ERR_M || ' Cannot insert duplicate oracle_emrep target ' || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(new URL = ' || :new.emd_url || ')' ); END IF; SELECT COUNT(type_meta_ver) INTO l_rep_cnt FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND NOT (type_meta_ver = :new.type_meta_ver); IF (l_rep_cnt > 0) THEN SELECT type_meta_ver INTO l_meta_ver FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND NOT (type_meta_ver = :new.type_meta_ver); BEGIN l_old_ver := TO_NUMBER(l_meta_ver); EXCEPTION WHEN OTHERS THEN l_old_ver := 0; END; BEGIN l_new_ver := TO_NUMBER(:new.type_meta_ver); EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.DUPLICATE_TARGET_ERR, MGMT_GLOBAL.DUPLICATE_TARGET_ERR_M || ' Invalid version oracle_emrep target ' || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(new URL = ' || :new.emd_url || ')' || '(old type_meta_ver = ' || l_meta_ver || ')' || '(new type_meta_ver = ' || :new.type_meta_ver); END; IF(l_old_ver > l_new_ver) THEN raise_application_error(MGMT_GLOBAL.DUPLICATE_TARGET_ERR, MGMT_GLOBAL.DUPLICATE_TARGET_ERR_M || ' Cannot regress version of oracle_emrep target ' || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(new URL = ' || :new.emd_url || ')' || '(old type_meta_ver = ' || l_meta_ver || ')' || '(new type_meta_ver = ' || :new.type_meta_ver); END IF; END IF; END IF; -- Bug 4580359: check that all the nodes of cluster are in same tz region. IF (emd_loader.is_multi_agent_target(:new.monitoring_mode) = 1) THEN BEGIN SELECT timezone_region INTO l_old_timezone_region FROM mgmt_targets WHERE target_name = :new.target_name AND target_type = :new.target_type; IF MGMT_GLOBAL.is_equivalent_tz(:new.timezone_region,l_old_timezone_region) = MGMT_GLOBAL.G_FALSE THEN raise_application_error(MGMT_GLOBAL.UPDATE_TZRGN_ERR, 'The timezone of the multiagent target (' || :new.target_name || ',' || :new.target_type || ')' || 'is not consistent with the timezone (' || l_old_timezone_region || ') reported by other agents.'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- Do nothing as the target is being added for the first time. END; END IF; -- Check target type is registered for the given meta version BEGIN EM_TARGET.check_type_ver_guid(:new.target_type,:new.type_meta_ver); EXCEPTION WHEN MGMT_GLOBAL.type_ver_not_exist THEN MGMT_TARGET.register_target_type(p_target_type_in => :new.target_type, p_type_meta_ver_in => :new.type_meta_ver); END; -- If the display_name is null, then default it to the name of the target IF (:new.display_name IS NULL) THEN :new.display_name := :new.target_name; END IF; -- If the timezone region is null, then default it to the timezone of repository IF (:new.timezone_region IS NULL) THEN :new.timezone_region := EM_TARGET.get_repository_timezone ; END IF; -- Initialize metric severity calculations for repository metrics l_rs_defined := em_rep_metric.is_repo_side_availability(:new.target_type, :new.type_meta_ver); IF (l_rs_defined) THEN :new.rep_side_avail := 1; END IF; -- Initialize the memberships history if this is a composite target -- NOTE: Unfortuntately we don't have a type property that tells -- us whether a target is composite or not. Once we add such a -- property, we can change this to be a conditional call. /* =-= disabled group history management as it is not being used em_hist_comptgt.init_memberships_hist(:new.target_guid, :new.timezone_region); */ -- Insert a proxy entry in MGMT_BLACKOUT_PROXY_TARGETS BEGIN INSERT INTO MGMT_BLACKOUT_PROXY_TARGETS(target_guid) VALUES (:new.target_guid); EXCEPTION -- Should not happen, since the entry should be deleted when the -- target is deleted WHEN DUP_VAL_ON_INDEX THEN NULL; END; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Never raise PK violation (DUP_VAL_ON_INDEX) from this trigger raise_application_error(MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR, MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR_M || ' Either the target is not cleaned completely or the deletion is still in progress. ' || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(target guid = ' || :new.target_guid || ')'); END; END; / show errors; rem This trigger will make sure there is an availability record for every new rem target that is loaded. This default record will be backdated to just rem before the longest reporting window starts. rem rem This is done for all targets except groups - this is defined currently rem as all targets that have is_groupset to 1. rem rem This trigger also inserts a row into the PING SUBSYSTEM. CREATE OR REPLACE TRIGGER targets_availability_default AFTER INSERT ON MGMT_TARGETS FOR EACH ROW DECLARE new_metric_guid RAW(16); l_is_composite NUMBER; l_is_group NUMBER; l_has_rs NUMBER; l_broken_msg mgmt_metric_errors.metric_error_message%TYPE; BEGIN BEGIN -- insert the defaul avail and marker if target type has R/S metric defined BEGIN SELECT count(*) INTO l_has_rs FROM mgmt_metrics WHERE target_type = :new.target_type AND type_meta_ver = :new.type_meta_ver AND (category_prop_1 = :new.category_prop_1 OR category_prop_1 = ' ') AND (category_prop_2 = :new.category_prop_1 OR category_prop_2 = ' ') AND (category_prop_3 = :new.category_prop_1 OR category_prop_3 = ' ') AND (category_prop_4 = :new.category_prop_1 OR category_prop_4 = ' ') AND (category_prop_5 = :new.category_prop_1 OR category_prop_5 = ' ') AND metric_name = mgmt_global.G_AVAIL_METRIC_NAME AND metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN ; -- Bug:4307553 - Availability is not applicable for system type target -- Pass "1" for is_group if system type propertiy exists l_is_group := :new.is_group; IF l_is_group = 0 THEN SELECT DECODE(count(*), 0, 0, 1) INTO l_is_group FROM mgmt_type_properties WHERE target_type = :new.target_type AND property_name = MGMT_GLOBAL.G_IS_SYSTEM_PROP ; END IF; IF l_has_rs > 0 THEN l_is_group := 0; END IF; -- BOOT STRAP SEVERITY/AVAILABILITY system for the target EM_SEVERITY.add_target(:new.target_guid, :new.target_type, l_is_group, :new.timezone_region); END; -- BOOT STRAP LOADER for this target EMD_LOADER.add_target(:new.target_guid); -- BOOT STRAP PURGE subsystem for this target EM_PURGE.add_target(:new.target_guid); -- Since HOST availability is no longer tracked at the EMD, we add the -- records for host availability tracking when the host record is first -- inserted. IF (:new.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN -- -- Check if a host/response/status metric has been defined. If not, we -- will set one up. Otherwise, continue on. -- EMD_LOADER.ADD_HOST_AVAIL_METRIC(p_target_guid => :new.target_guid, p_type_meta_ver => :new.type_meta_ver, p_category_prop_1 => :new.category_prop_1, p_category_prop_2 => :new.category_prop_2, p_category_prop_3 => :new.category_prop_3, p_category_prop_4 => :new.category_prop_4, p_category_prop_5 => :new.category_prop_5); ELSIF (:new.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE) THEN -- BOOT STRAP PING SYSTEM EM_PING.add_target(:new.target_guid, :new.timezone_region); END IF; -- Delete any duplicate entries for this target guid DELETE FROM MGMT_DUPLICATE_TARGETS WHERE target_guid = :new.target_guid AND duplicate_emd_url = :new.emd_url; -- Remeber the newly added target EMD_LOADER.p_targets_added.EXTEND; EMD_LOADER.p_targets_added(EMD_LOADER.p_targets_added.LAST) := SMP_EMD_NVPAIR( :new.target_name, :new.target_type); --Bug:4260179 - Broken target to be marked as metric error start -- Target entering to Broken State --IF ( NVL(TRIM(:NEW.broken_reason), 0) != MGMT_GLOBAL.G_NOT_BROKEN ) THEN IF EMD_LOADER.is_fatally_broken(:NEW.broken_reason) THEN l_broken_msg := 'Target is in broken state. Reason - '||:NEW.broken_str ; -- Remeber the target whose broken state has changed EMD_LOADER.p_targets_broken.EXTEND; EMD_LOADER.p_targets_broken(EMD_LOADER.p_targets_broken.LAST) := MGMT_BROKEN_TARGET_OBJ( :new.target_guid, l_broken_msg); END IF; -- Remeber the targets added/updated EMD_LOADER.p_targets_loaded.EXTEND; EMD_LOADER.p_targets_loaded(EMD_LOADER.p_targets_loaded.LAST) := SMP_EMD_NVPAIR( :new.target_name, :new.target_type); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Never raise PK violation (DUP_VAL_ON_INDEX) from this trigger -- Catch and raise an application error instead. raise_application_error(MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR, MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR_M || ' Either the target is not cleaned completely or the deletion is still in progress. ' || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(target guid = ' || :new.target_guid || ')'); END; END; / show errors; CREATE OR REPLACE TRIGGER check_duplicate_targets BEFORE UPDATE ON MGMT_TARGETS FOR EACH ROW DECLARE l_cnt_url NUMBER := 0; l_avail_cnt NUMBER := 0; l_old_agent_name mgmt_targets.emd_url%TYPE; l_old_agent_port VARCHAR2(10); l_new_agent_name mgmt_targets.emd_url%TYPE; l_new_agent_port VARCHAR2(10); l_broken_msg mgmt_metric_errors.metric_error_message%TYPE; l_old_catprop_array mgmt_short_string_array := mgmt_short_string_array() ; l_new_catprop_array mgmt_short_string_array := mgmt_short_string_array() ; BEGIN -- Check target type is registered for the given meta version IF(NOT (:old.type_meta_ver = :new.type_meta_ver)) THEN BEGIN EM_TARGET.check_type_ver_guid(:new.target_type,:new.type_meta_ver); EXCEPTION WHEN MGMT_GLOBAL.type_ver_not_exist THEN MGMT_TARGET.register_target_type(p_target_type_in => :new.target_type, p_type_meta_ver_in => :new.type_meta_ver); END; END IF; IF ( NOT (:new.target_type = 'oracle_emrep' )) THEN IF ( NOT (:old.emd_url = :new.emd_url)) THEN emd_loader.get_name_port_from_emd_url(:OLD.emd_url, l_old_agent_name, l_old_agent_port); emd_loader.get_name_port_from_emd_url(:NEW.emd_url, l_new_agent_name, l_new_agent_port); IF NOT ((l_old_agent_name = l_new_agent_name) AND (l_old_agent_port = l_new_agent_port)) THEN IF (emd_loader.is_emd_url_change_allowed(:old.emd_url, :new.emd_url) = FALSE) THEN -- raise application error for the loader to handle.. raise_application_error(MGMT_GLOBAL.DUPLICATE_TARGET_ERR, MGMT_GLOBAL.DUPLICATE_TARGET_ERR_M || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(new URL = ' || :new.emd_url || ')' || '(old URL = ' || :old.emd_url || ')' ); END IF; END IF; END IF; END IF; :new.last_updated_time := SYSDATE; -- If a new metaver for host is added, add the response metric -- for that version IF ( (:new.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) AND (:new.type_meta_ver <> :old.type_meta_ver) ) THEN -- -- Check if a host/response/status metric has been defined. If not, -- create one. -- EMD_LOADER.ADD_HOST_AVAIL_METRIC(p_target_guid => :new.target_guid, p_type_meta_ver => :new.type_meta_ver, p_category_prop_1 => :new.category_prop_1, p_category_prop_2 => :new.category_prop_2, p_category_prop_3 => :new.category_prop_3, p_category_prop_4 => :new.category_prop_4, p_category_prop_5 => :new.category_prop_5); END IF; -- If old timezone region is NULL, allow the update --Bug:4488153 - Retain :OLD TZR if :NEW is NULL IF (:new.timezone_region IS NOT NULL) THEN IF ( (:old.timezone_region IS NOT NULL) AND (NOT (:new.timezone_region = :old.timezone_region)) ) THEN -- If new timezone region is not the same as old timezone region -- and we are not in the process of changing the timezone, raise -- an error. -- Exclude oracle_emrep target and targets with no availability history -- from this check SELECT COUNT(*) INTO l_avail_cnt FROM mgmt_availability WHERE target_guid = :new.target_guid; IF ( (emd_loader.allow_tzrgn_change = FALSE) AND (l_avail_cnt > 1) AND ( NOT (:new.target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE )) ) THEN -- raise application error for the loader to handle.. raise_application_error(MGMT_GLOBAL.UPDATE_TZRGN_ERR, MGMT_GLOBAL.UPDATE_TZRGN_ERR_M || '(target name = ' || :new.target_name || ')' || '(target type = ' || :new.target_type || ')' || '(new tzrgn = ' || :new.timezone_region || ')' || '(old tzrgn = ' || :old.timezone_region || ')' ); END IF; END IF; ELSE :new.timezone_region := :old.timezone_region ; END IF; -- We currently do not support changing the monitoring mode on the -- fly. Always keep the original monitoring mode value. IF (:new.monitoring_mode IS NULL) THEN :new.monitoring_mode := :old.monitoring_mode; ELSIF (:new.monitoring_mode <> :old.monitoring_mode) THEN :new.monitoring_mode := :old.monitoring_mode; MGMT_LOG.LOG_ERROR('LOADER', 0, 'Attempt to modify monitoring mode for target ' || :new.target_name || ' is ignored.'); END IF; -- Do not allow blanking category properties. This is to handle the -- scenario where certain category properties are defined as dynamic -- properties and the dynamic properties cannot be evaluated because -- of target being done and as a result agent sending blank values -- even for valid values. emd_loader.check_cat_prop_changes('category_prop_1', :new.target_name, :old.category_prop_1, :new.category_prop_1); emd_loader.check_cat_prop_changes('category_prop_2', :new.target_name, :old.category_prop_2, :new.category_prop_2); emd_loader.check_cat_prop_changes('category_prop_3', :new.target_name, :old.category_prop_3, :new.category_prop_3); emd_loader.check_cat_prop_changes('category_prop_4', :new.target_name, :old.category_prop_4, :new.category_prop_4); emd_loader.check_cat_prop_changes('category_prop_5', :new.target_name, :old.category_prop_5, :new.category_prop_5); IF :new.type_meta_ver != :old.type_meta_ver OR :old.category_prop_1 != :new.category_prop_1 OR :old.category_prop_2 != :new.category_prop_2 OR :old.category_prop_3 != :new.category_prop_3 OR :old.category_prop_4 != :new.category_prop_4 OR :old.category_prop_5 != :new.category_prop_5 THEN l_old_catprop_array := mgmt_short_string_array(:old.category_prop_1, :old.category_prop_2, :old.category_prop_3, :old.category_prop_4, :old.category_prop_5) ; l_new_catprop_array := mgmt_short_string_array(:new.category_prop_1, :new.category_prop_2, :new.category_prop_3, :new.category_prop_4, :new.category_prop_5) ; emd_loader.p_metaver_changes.extend(1) ; emd_loader.p_metaver_changes(emd_loader.p_metaver_changes.COUNT) := mgmt_target_meta_ver_cbk_obj.new(p_target_guid=>:new.target_guid, p_target_type=>:new.target_type, p_from_meta_ver=>:old.type_meta_ver, p_to_meta_ver=>:new.type_meta_ver, p_from_catprop_array=>l_old_catprop_array, p_to_catprop_array=>l_new_catprop_array ) ; END IF ; --Bug:4260179 - Broken target to be marked as metric error start -- If OLD and NEW broken str are not same then IF NVL(TRIM(:OLD.broken_reason), 0) != NVL(TRIM(:NEW.broken_reason), 0) THEN -- Target entering to Broken State OR received consecutive, but different broken reason --IF ( NVL(TRIM(:NEW.broken_reason), 0) != MGMT_GLOBAL.G_NOT_BROKEN ) THEN IF EMD_LOADER.is_fatally_broken(:NEW.broken_reason) THEN l_broken_msg := 'Target is in broken state. Reason - '||:NEW.broken_str ; -- Remeber the target whose broken state has changed EMD_LOADER.p_targets_broken.EXTEND; EMD_LOADER.p_targets_broken(EMD_LOADER.p_targets_broken.LAST) := MGMT_BROKEN_TARGET_OBJ( :new.target_guid, l_broken_msg); -- Target leaving broken state or NON-FATAL broken state has occurred ELSIF NOT (EMD_LOADER.is_fatally_broken(:NEW.broken_reason)) AND (EMD_LOADER.is_fatally_broken(:OLD.broken_reason)) THEN /* New reason is NON-FATAL and OLD is FATAL*/ l_broken_msg := NULL; -- Remeber the target whose broken state has changed EMD_LOADER.p_targets_broken.EXTEND; EMD_LOADER.p_targets_broken(EMD_LOADER.p_targets_broken.LAST) := MGMT_BROKEN_TARGET_OBJ( :new.target_guid, l_broken_msg); END IF; END IF; -- Remeber the targets added/updated EMD_LOADER.p_targets_loaded.EXTEND; EMD_LOADER.p_targets_loaded(EMD_LOADER.p_targets_loaded.LAST) := SMP_EMD_NVPAIR( :new.target_name, :new.target_type); END; / show errors; CREATE OR REPLACE TRIGGER em_targets_delete BEFORE DELETE ON MGMT_TARGETS FOR EACH ROW DECLARE l_cnt NUMBER := 0; BEGIN -- Disallow deleting rows from mgmt_targets table. The only acceptable means -- of deleting a target is through delete_target API -- Check to see if the target has entry in mgmt_targets_delete table SELECT COUNT(*) INTO l_cnt FROM MGMT_TARGETS_DELETE WHERE target_guid = :old.target_guid AND delete_complete_time IS NULL; IF (l_cnt = 0) THEN -- If no delete requests found, raise application error to disallow raise_application_error(MGMT_GLOBAL.ACTION_NOT_SUPP_ERR, MGMT_GLOBAL.ACTION_NOT_SUPP_ERR_M || ' Target deletion by simply deleting from MGMT_TARGETS table is not supported. ' || 'Use mgmt_admin.delete_target() API to delete targets.'); END IF; END; / show errors rem rem If the metric guid is null, then get it from the metrics table from the rem row for the metric itself (as opposed to the columns) rem rem Update the current_metric_errors table based on the message being entered rem rem If error_type =0 and message is NOT NULL then its "metric error start" rem If error_type =1 and message is NOT NULL i.e warning then its "metric error end" rem If error_message is NULL i.e clear then its "metric error end" or "Warning end" CREATE OR REPLACE TRIGGER metric_errors_cur_and_dupes BEFORE INSERT ON MGMT_METRIC_ERRORS FOR EACH ROW DECLARE current_availability_rowid ROWID; current_availability_status NUMBER; update_availability BOOLEAN; duplicate_error BOOLEAN := FALSE; last_error_message VARCHAR2(4000); cur_metric_guid RAW(16); l_avail_metric_guid RAW(16); l_cnt_avail_guids NUMBER; is_raw_metric NUMBER; l_error_metric_guid MGMT_METRICS.target_type%TYPE; l_target_type MGMT_METRICS.target_type%TYPE; l_metric_name MGMT_METRICS.metric_name%TYPE; l_bcn_msg MGMT_METRIC_ERRORS.metric_error_message%TYPE; l_met_info EM_METRIC.METRIC_INFO_REC; l_last_error_type NUMBER := -1; -- -- Check if this is an availability (Response/Status) metric -- CURSOR check_avail_metric(v_metric_guid_in RAW) IS SELECT count(*) FROM MGMT_METRICS WHERE metric_guid = v_metric_guid_in AND metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME; -- -- Get Availability (Response/Status) metric guid for the given target guid -- CURSOR get_avail_guid(v_target_guid_in RAW) IS SELECT m.metric_guid FROM MGMT_TARGETS t, MGMT_METRICS m WHERE t.target_type = m.target_type AND t.target_guid = :new.target_guid AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN; -- -- Return all non-key and non-table rows for this metric -- -- NOTE: Do not clear current metrics for metrics whose keys_from_mult_cols = 1 -- CURSOR get_metric_columns (v_target_guid_in RAW, v_metric_name_in VARCHAR2) IS SELECT m.metric_guid FROM MGMT_METRICS m, MGMT_TARGETS t WHERE m.metric_name = v_metric_name_in AND t.target_guid = v_target_guid_in AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') AND m.keys_from_mult_colls = 0 AND m.key_order = 0 AND (m.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_NUMBER OR m.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_STRING); BEGIN -- Default the collection name and agent guid IF (:new.coll_name IS NULL) THEN :new.coll_name := ' '; END IF; IF (:new.agent_guid IS NULL) THEN :new.agent_guid := HEXTORAW('00000000000000000000000000000000'); END IF; IF (EMDW_LOG.P_IS_DEBUG_SET)THEN EMDW_LOG.DEBUG('metric_errors_cur_and_dupes trigger: Entry ' || ' target guid = [' || :new.target_guid || ']' || ' metric guid = [' || :new.metric_guid || ']' || ' coll name = [' || :new.coll_name || ']' || ' agent guid = [' || :new.agent_guid || ']' || ' coll ts = [' || TO_CHAR(:new.collection_timestamp, 'YYYY-MM-DD HH24:MI:SS') || ']' || ' error typ = [' || :new.metric_error_type || ']' || ' message = [' || :new.metric_error_message || ']', EM_METRIC.G_MODULE_NAME); END IF; -- if the metric error is for a beacon proxy collection, use the coll_name and agent_guid -- fields to construct a more informative error message IF :new.metric_error_message IS NOT NULL THEN MGMT_GENSVC_AVAIL.METRIC_ERROR_MSG(:new.target_guid, :new.metric_guid, :new.coll_name, :new.agent_guid, :new.metric_error_message, l_bcn_msg); IF l_bcn_msg IS NOT NULL THEN :new.metric_error_message := l_bcn_msg; END IF; END IF; BEGIN SELECT metric_error_message, metric_error_type INTO last_error_message, l_last_error_type FROM MGMT_CURRENT_METRIC_ERRORS WHERE target_guid = :new.target_guid AND metric_guid = :new.metric_guid AND coll_name = :new.coll_name AND agent_guid = :new.agent_guid; -- -- If the last error message is the same as the one that we are -- currently inserting, then the error record is a duplicate. -- Raise a duplicate exception. It will be processed by the -- loader. -- IF (last_error_message IS NULL) THEN -- If the last and new message are NULL, mark the new one as -- duplicate IF (:new.metric_error_message IS NULL) THEN duplicate_error := TRUE; END IF; ELSE IF last_error_message = :new.metric_error_message AND l_last_error_type = :new.metric_error_type THEN duplicate_error := TRUE; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- -- When there are no metric errors. and we received a null error message, -- mark that as a duplicate -- IF (:new.metric_error_message IS NULL) THEN duplicate_error := TRUE; END IF; END; IF (duplicate_error = TRUE) THEN raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR, MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M || ' The last error message is the same as this error message for : ' || '(target guid = ' || :new.target_guid || ')' || '(metric guid = ' || :new.metric_guid || ')' || '(error_msg = ' || :new.metric_error_message || ')' ); END IF; -- -- Ok, if we made it to here, then the record isn't a duplicate, so -- let's update the current error table for the non empty error messages -- severity codes. This section of the code is optimized for update -- peformance, since that is the most typical case. -- -- Verify that this is a target's Response metric value. This -- will let us know if we have to log an availability record. -- OPEN check_avail_metric(:new.metric_guid); FETCH check_avail_metric INTO l_cnt_avail_guids; IF (check_avail_metric%ISOPEN) THEN CLOSE check_avail_metric; END IF; IF (l_cnt_avail_guids > 0) THEN -- -- Get the response status target_guid for this target type -- OPEN get_avail_guid(:new.target_guid); FETCH get_avail_guid INTO l_avail_metric_guid; IF (get_avail_guid%ISOPEN) THEN CLOSE get_avail_guid; END IF; update_availability := TRUE; ELSE update_availability := FALSE; END IF; -- "metric error start" only if error_type = 0 IF ( :new.metric_error_message IS NOT NULL) AND (:new.metric_error_type = MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR) THEN -- -- last_error_message is NOT NULL indicates that record exists so update it else insert it -- we want to optimize for that case. -- IF last_error_message IS NOT NULL THEN UPDATE MGMT_CURRENT_METRIC_ERRORS SET metric_error_message = :new.metric_error_message, metric_error_type = :new.metric_error_type, collection_timestamp = :new.collection_timestamp WHERE target_guid = :new.target_guid AND metric_guid = :new.metric_guid AND coll_name = :new.coll_name AND agent_guid = :new.agent_guid; ELSE INSERT INTO MGMT_CURRENT_METRIC_ERRORS (target_guid, metric_guid, coll_name, agent_guid, collection_timestamp, metric_error_message, metric_error_type) VALUES (:new.target_guid, :new.metric_guid, :new.coll_name, :new.agent_guid, :new.collection_timestamp, :new.metric_error_message, :new.metric_error_type); END IF; -- -- Blank out current metrics on error. -- We should not add to current_metrics if the metric the error -- occurred on a raw metric -- -- Get metric info EM_METRIC.get_metric_info_for_target(:new.metric_guid, :new.target_guid, l_met_info); IF (EMDW_LOG.P_IS_DEBUG_SET)THEN EMDW_LOG.DEBUG('metric_errors_cur_and_dupes trigger: Got metric info ' || ' metric guid = [' || :new.metric_guid || ']' || ' metric name = [' || l_met_info.metric_name || ']' || ' metric typ = [' || l_met_info.metric_type || ']' || ' num keys = [' || l_met_info.num_keys || ']', EM_METRIC.G_MODULE_NAME); END IF; IF ( (l_met_info.metric_name IS NOT NULL) AND (l_met_info.metric_type <> MGMT_GLOBAL.G_METRIC_TYPE_RAW) ) THEN OPEN get_metric_columns (:new.target_guid, l_met_info.metric_name); LOOP FETCH get_metric_columns into l_error_metric_guid; EXIT WHEN get_metric_columns%NOTFOUND; -- -- Blank out current metrics for all key values on error -- for this metric_name, metric_col -- UPDATE MGMT_CURRENT_METRICS SET value = NULL, string_value = NULL, collection_timestamp = :new.collection_timestamp WHERE target_guid = :new.target_guid AND metric_guid = l_error_metric_guid; -- -- If there are no records currently, no current metric values have been -- uploaded for this metric - lets add in the initial set -- IF SQL%ROWCOUNT = 0 THEN -- Add the initial set only for zero key metrics IF ( l_met_info.num_keys = 0 ) THEN INSERT INTO MGMT_CURRENT_METRICS (target_guid, collection_timestamp, metric_guid) VALUES (:new.target_guid, :new.collection_timestamp, l_error_metric_guid); END IF; -- num_keys = 0 END IF; END LOOP; IF (get_metric_columns%ISOPEN) THEN CLOSE get_metric_columns; END IF; END IF; -- -- Insert a new ERROR START severity record. -- IF update_availability = TRUE THEN BEGIN INSERT INTO mgmt_violations (target_guid, policy_guid, collection_timestamp, violation_level, message) VALUES (:new.target_guid, l_avail_metric_guid, :new.collection_timestamp, MGMT_GLOBAL.G_SEVERITY_ERROR_START, 'Metric evaluation error start - '||:new.metric_error_message); EXCEPTION -- Ignore duplicate severity error as we are accepting two consecutive metric error but not severity WHEN MGMT_GLOBAL.duplicate_record THEN NULL; END; END IF; -- END IF update_availability = TRUE -- -- Recompute beacon availability -- IF (:new.metric_error_type = 0) THEN MGMT_GENSVC_AVAIL.PROCESS_METRIC_ERROR( :new.target_guid, :new.metric_guid, :new.coll_name ); END IF; -- "metric error end" only if its clear i.e message is NULL or its warning i.e error_type = 1 ELSIF ((:new.metric_error_message IS NOT NULL) AND (:new.metric_error_type = MGMT_GLOBAL.G_METRIC_ERROR_TYPE_WARN)) OR (:new.metric_error_message IS NULL) THEN -- -- Remove the error record from current errors table because it -- has been cleared. -- last_error_message is NOT NULL indicates there is record in table so delete it. -- IF :new.metric_error_message is NULL AND last_error_message IS NOT NULL THEN DELETE FROM MGMT_CURRENT_METRIC_ERRORS WHERE target_guid = :new.target_guid AND metric_guid = :new.metric_guid AND coll_name = :new.coll_name AND agent_guid = :new.agent_guid; ELSE -- -- Its warning then -- If there is record in mgmt_current_metric_errors then update else insert -- last_error_message is NOT NULL indicates record exists in table so update it -- If last_error_message is NULL indicates record does not exist in table so insert it -- IF last_error_message IS NOT NULL THEN UPDATE MGMT_CURRENT_METRIC_ERRORS SET metric_error_message = :new.metric_error_message, metric_error_type = :new.metric_error_type, collection_timestamp = :new.collection_timestamp WHERE target_guid = :new.target_guid AND metric_guid = :new.metric_guid AND coll_name = :new.coll_name AND agent_guid = :new.agent_guid; ELSIF last_error_message IS NULL THEN INSERT INTO MGMT_CURRENT_METRIC_ERRORS (target_guid, metric_guid, coll_name, agent_guid, collection_timestamp, metric_error_message, metric_error_type) VALUES (:new.target_guid, :new.metric_guid, :new.coll_name, :new.agent_guid, :new.collection_timestamp, :new.metric_error_message, :new.metric_error_type); END IF; END IF; --:new.metric_error_message IS NULL -- -- If the error is being cleared... -- -- Insert a ERROR_END severity record, which inturn -- affects the availability of the target. -- In both warning and clear case we should end error -- l_last_error_type = 0 indicates that error start cond. exists so put error end. IF update_availability = TRUE AND l_last_error_type = MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR AND last_error_message IS NOT NULL THEN INSERT INTO mgmt_violations (target_guid, policy_guid, collection_timestamp, violation_level, message) VALUES (:new.target_guid, l_avail_metric_guid, :new.collection_timestamp, MGMT_GLOBAL.G_SEVERITY_ERROR_END, 'Metric evaluation error end'); END IF; -- END IF update_availability = TRUE -- -- Recompute beacon availability -- IF (:new.metric_error_type = 0) THEN MGMT_GENSVC_AVAIL.PROCESS_METRIC_ERROR( :new.target_guid, :new.metric_guid, :new.coll_name ); END IF; END IF; -- END of ELSE error cleared THEN IF (EMDW_LOG.P_IS_DEBUG_SET)THEN EMDW_LOG.DEBUG('metric_errors_cur_and_dupes trigger: Exit ' || ' target guid = [' || :new.target_guid || ']' || ' metric guid = [' || :new.metric_guid || ']' || ' coll name = [' || :new.coll_name || ']' || ' agent guid = [' || :new.agent_guid || ']' || ' coll ts = [' || TO_CHAR(:new.collection_timestamp, 'YYYY-MM-DD HH24:MI:SS') || ']', EM_METRIC.G_MODULE_NAME); END IF; END; / show errors; CREATE OR REPLACE TRIGGER mgmt_target_property_change AFTER UPDATE ON MGMT_TARGET_PROPERTIES FOR EACH ROW WHEN ( NVL(new.property_value,' ') ! = NVL(old.property_value,' ')) BEGIN mgmt_properties.exec_property_change_callbacks (p_target_guid=>:new.target_guid, p_property_name=>:new.property_name, p_old_property_value=>:old.property_value, p_new_property_value=>:new.property_value ) ; END mgmt_target_property_change ; / show errors