Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\jobs\jobs_triggers.sql
Rem drv: <create type="triggers" pos="group/group_triggers.sql+"/> Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_triggers.sql /st_emcore_10.2.0.4.2db11.2/2 2008/10/28 16:22:51 rahgupta Exp $ Rem Rem jobs_triggers.sql Rem Rem Copyright (c) 2002, 2008, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem jobs_triggers.sql - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> Rem Rem MODIFIED (MM/DD/YY) Rem rahgupta 10/27/08 - Bug 7479325 Rem lsatyapr 10/09/08 - Call MJE.update_ref_cnt for large params Rem neearora 12/04/07 - XbranchMerge neearora_bug-6640129_fix from Rem st_emcore_10.2.0.1.0 Rem neearora 11/27/07 - bug 6640129 Rem rdabbott 03/13/07 - review Rem rdabbott 07/19/07 - specify which jobs are using undeletable jobtype Rem rdabbott 07/28/07 - Backport rdabbott_bug-6194784 from main Rem shnavane 07/18/07 - Fix bug #6034526 Rem shnavane 07/23/07 - Backport shnavane_bug-6034526 from main Rem neearora 06/20/07 - bug 6142074 Rem neearora 05/22/07 - added l_an_ack_sev in call to Rem QUEUE_METRIC_NOTIFICATIONS Rem neearora 03/30/07 - enqueue job_state_change_guid Rem kmanicka 07/19/06 - Backport kmanicka_bug-5056981 from main Rem skini 03/08/06 - Refine fix for 5071610 Rem skini 03/07/06 - Account for suspended states Rem skini 03/01/06 - Do not insert rows into MGMT_JOB_STATE_CHANGES Rem on system job retry (bug 5071610) Rem skini 03/09/06 - Backport skini_bug-5071610 from main Rem kmanicka 02/23/06 - bug 5056981 throw exception job_using_jobtype_delete Rem dsahrawa 08/05/05 - bug 4506779, only fix flatten steps if session Rem variable is set Rem dsahrawa 07/21/05 - bug 4496616, add support for vector and large Rem parameters for tasks Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem skini 06/10/05 - Fix bug 4421503 Rem dsahrawa 02/22/05 - add insert/update trigger on execplan Rem shianand 02/10/05 - Audit Job Step 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 ktlaw 01/11/05 - add repmgr header Rem skini 12/14/04 - Adjust end time so that it is at least equal to Rem start Rem skini 11/24/04 - Deleted from mgmt_credentials2 when deleting Rem from nested_job_cred_info Rem jaysmith 11/10/04 - move notification queue out of trigger for CA Rem insert Rem jaysmith 10/28/04 - comment out violation query in CA exec trigger Rem skini 10/08/04 - Versioning changes Rem dcawley 09/28/04 - State changes for corrective actions Rem skini 09/17/04 - Prevent 3113 in cred_params trigger Rem skini 08/08/04 - Add update trigger for output and error ids Rem skini 07/26/04 - Add additional metadata for credential sources Rem dcawley 07/20/04 - Change constant for job state changes Rem skini 07/16/04 - Populate state change table Rem dsahrawa 06/22/04 - make status bucket calculation conditional Rem aholser 03/18/04 - add oms name Rem skini 09/30/03 - Add status bucket column Rem skini 09/08/03 - timezone changes Rem skini 06/12/03 - large param support Rem skini 05/13/03 - Add execution_id to targets table, allow targets to be edited Rem skini 10/09/02 - Continue work on 2549136 Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem Trigger EXECPLAN_INSERT_TRIGGER rem rem This trigger ensures that entries in MGMT_JOB_EXECPLAN rem that are have origins that are nested single target jobs rem have their origins changed in cases when we generate rem iterative parallel stepsets for the nested jobs. rem CREATE OR REPLACE TRIGGER execplan_insert_trigger BEFORE INSERT ON MGMT_JOB_EXECPLAN FOR EACH ROW DECLARE l_flattened_origin MGMT_JOB_EXECPLAN.flattened_targets%TYPE := 0; BEGIN IF MGMT_JOB_ENGINE.fixing_flatten_steps THEN IF :new.step_type = MGMT_JOB_ENGINE.STEPTYPE_FLATTEN_TARGETS_STEP THEN IF :new.flattened_targets = 1 THEN UPDATE MGMT_JOB_EXECPLAN SET num_children = num_children + 1 WHERE job_type_id = :new.job_type_id AND step_name = :new.stepset_name AND step_type = MGMT_JOB_ENGINE.STEPTYPE_ITPLL_STEPSET; ELSE UPDATE MGMT_JOB_EXECPLAN SET num_children = num_children + 1 WHERE job_type_id = :new.job_type_id AND step_name = :new.stepset_name; END IF; END IF; IF :new.origin_step_name IS NOT NULL AND :new.origin_step_type = MGMT_JOB_ENGINE.STEPTYPE_JOB THEN SELECT flattened_targets INTO l_flattened_origin FROM MGMT_JOB_EXECPLAN WHERE job_type_id = :new.job_type_id AND step_name = :new.origin_step_name AND step_type = MGMT_JOB_ENGINE.STEPTYPE_JOB; IF l_flattened_origin = 1 THEN :new.origin_step_type := MGMT_JOB_ENGINE.STEPTYPE_ITPLL_STEPSET; END IF; END IF; END IF; END; / show errors; rem rem Trigger EXECPLAN_DELETE_TRIGGER rem rem This trigger ensures that entries in MGMT_JOB_STEP_PARAMS rem and MGMT_NESTED_JOB_TARGETS are deleted when a step rem is deleted from the MGMT_JOB_EXECPLAN table rem CREATE OR REPLACE TRIGGER execplan_delete_trigger AFTER DELETE ON MGMT_JOB_EXECPLAN FOR EACH ROW BEGIN DELETE FROM MGMT_JOB_STEP_PARAMS WHERE job_type_id=:old.job_type_id AND step_name=:old.step_name; DELETE FROM MGMT_NESTED_JOB_TARGETS WHERE job_type_id=:old.job_type_id AND step_name=:old.step_name; DELETE FROM MGMT_JOB_PARAM_SOURCE WHERE job_type_id=:old.job_type_id; DELETE FROM MGMT_JOB_SEC_INFO WHERE job_type_id=:old.job_type_id; END; / show errors; rem rem Trigger job_summ_ins_trigger rem rem This trigger ensures that the status_bucket column rem is updated with the correct values in the job_exec_summary table rem CREATE OR REPLACE TRIGGER job_summ_ins_trigger BEFORE INSERT OR UPDATE ON MGMT_JOB_EXEC_SUMMARY FOR EACH ROW BEGIN :new.status_bucket := MGMT_JOBS.get_status_bucket(:new.status); END; / show errors CREATE OR REPLACE TRIGGER job_summ_ins_trigger2 AFTER INSERT OR UPDATE ON MGMT_JOB_EXEC_SUMMARY FOR EACH ROW DECLARE l_is_ca NUMBER:= 0; l_change_guid RAW(16) := SYS_GUID(); l_now DATE := SYSDATE; l_violation_guid RAW(16) := NULL; l_violation MGMT_VIOLATIONS%ROWTYPE; l_change MGMT_JOB_STATE_CHANGES%ROWTYPE; l_source_type NUMBER := EMD_NOTIFICATION.JOB_STATE_CHANGE; l_count NUMBER; l_system_job NUMBER; BEGIN IF INSERTING OR :new.status != :old.status THEN SELECT system_job INTO l_system_job FROM MGMT_JOB WHERE job_id=:new.job_id; -- First check to see for scheduled and running states, whether -- an entry has already been inserted for this execution. This could -- happen for system jobs that are retried, as well as agent-bound -- jobs where a step is retried when the OMS is unable to contact -- the agent IF :new.status IN (MGMT_JOB_ENGINE.SCHEDULED_STATUS, MGMT_JOB_ENGINE.EXECUTING_STATUS) THEN SELECT COUNT(1) INTO l_count FROM MGMT_JOB_STATE_CHANGES WHERE execution_id=:new.execution_id AND newstate=:new.status; -- Do not log state changes where we go from scheduled to -- running and vice-versa. However, we do need to log -- changes where we go to scheduled or running from other -- states (eg., suspended) IF l_count >0 AND :old.status IN (MGMT_JOB_ENGINE.SCHEDULED_STATUS, MGMT_JOB_ENGINE.EXECUTING_STATUS, MGMT_JOB_ENGINE.COMPLETED_STATUS, MGMT_JOB_ENGINE.FAILED_STATUS, MGMT_JOB_ENGINE.ABORTED_STATUS) THEN RETURN; END IF; ELSIF :new.status IN (MGMT_JOB_ENGINE.FAILED_STATUS, MGMT_JOB_ENGINE.ABORTED_STATUS) AND l_system_job=MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY THEN -- For system jobs that will be retried, do not insert -- into MGMT_JOB_STATE_CHANGES, since the status will go -- back to running anyway RETURN; END IF; -- Check if this is a corrective action SELECT is_corrective_action INTO l_is_ca FROM MGMT_JOB WHERE job_id = :new.job_id; -- If it is a corrective action then is it for a policy or metric IF l_is_ca = 1 AND NOT INSERTING THEN SELECT * INTO l_violation FROM MGMT_VIOLATIONS WHERE violation_guid = :new.triggering_severity; l_violation_guid := :new.triggering_severity; IF l_violation.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY THEN l_source_type := EMD_NOTIFICATION.POLICY_CA_STATE_CHANGE; ELSE l_source_type := EMD_NOTIFICATION.METRIC_CA_STATE_CHANGE; END IF; END IF; -- We don't do this in the trigger if inserting a CA, because -- we'll be called in the context of another trigger on -- mgmt_violations, so we can't get the violation_guid. -- (In this case, the state-change is inserted in -- MGMT_JOB_ENGINE.insert_ca_state_change, instead) IF l_is_ca = 0 OR NOT INSERTING THEN INSERT INTO MGMT_JOB_STATE_CHANGES(state_change_guid, job_id, execution_id, step_id, logged, occurred, newstate, status_bucket, type, violation_guid) VALUES (l_change_guid, :new.job_id, :new.execution_id, -1, l_now, l_now, :new.status, :new.status_bucket, l_source_type, l_violation_guid); -- do not add job_state_change guid in notify_input_q in case of db control as -- dbcontrol directly call QUEUE_JOB_NOTIFICATIONS / QUEUE_METRIC_NOTIFICATIONS. IF (EMD_MAINTENANCE.IS_CENTRAL_MODE != 0) THEN EMD_NOTIFICATION.QUEUE_NOTIF_INPUT(l_change_guid, EMD_NOTIFICATION.GUID_TYPE_JOB_STATE_CHANGE); END IF; -- Add the state change to the notification queue for stand alone mode IF (EMD_MAINTENANCE.IS_CENTRAL_MODE = 0) THEN l_change.state_change_guid := l_change_guid; l_change.job_id := :new.job_id; l_change.execution_id := :new.execution_id; l_change.step_id := -1; l_change.logged := l_now; l_change.occurred := l_now; l_change.newstate := :new.status; l_change.status_bucket := :new.status_bucket; l_change.type := l_source_type; l_change.violation_guid := l_violation_guid; IF l_is_ca = 0 THEN EMD_NOTIFICATION.QUEUE_JOB_NOTIFICATIONS(l_change); ELSE EMD_NOTIFICATION.QUEUE_METRIC_NOTIFICATIONS(l_violation, l_change); END IF; END IF; END IF; END IF; END; / show errors rem rem Trigger JOB_DELETE_TRIGGER rem rem This trigger ensures that entries in MGMT_JOB_TARGET rem and MGMT_JOB_PARAMETER are cleaned up when a job rem is deleted from the MGMT_JOB table rem CREATE OR REPLACE TRIGGER job_delete_trigger AFTER DELETE ON MGMT_JOB FOR EACH ROW BEGIN DELETE FROM MGMT_JOB_PARAMETER where job_id=:old.job_id; DELETE FROM MGMT_JOB_TARGET where job_id=:old.job_id; DELETE FROM MGMT_JOB_EXT_TARGETS where job_id=:old.job_id; END; / show errors; rem rem Trigger JOB_TARGET_INSERT_TRIGGER rem rem This trigger ensures that the target is also inserted into rem the extended target list of the job rem CREATE OR REPLACE TRIGGER job_target_insert_trigger AFTER INSERT ON MGMT_JOB_TARGET FOR EACH ROW BEGIN MGMT_JOB_ENGINE.insert_ext_target(:new.job_id, :new.execution_id, :new.target_list_index, :new.target_guid); END; / show errors; rem rem Trigger JOB_TARGET_DEL_TRIGGER rem rem This trigger ensures that the target is also removed into rem the extended target list of the job. provided no other rem parameter is referring to it rem CREATE OR REPLACE TRIGGER job_target_del_trigger AFTER DELETE ON MGMT_JOB_TARGET FOR EACH ROW BEGIN MGMT_JOB_ENGINE.delete_ext_target(:old.job_id, :old.execution_id, :old.target_list_index, :old.target_guid); END; / show errors; -- Insert and update triggers for the execution table: propogate the -- information into history as well. CREATE OR REPLACE TRIGGER mgmt_job_exec_insert AFTER INSERT ON MGMT_JOB_EXECUTION FOR EACH ROW BEGIN INSERT INTO MGMT_JOB_HISTORY(job_id, execution_id, step_id, source_step_id, original_step_id, restart_mode, step_name, step_type, command_type, iterate_param, iterate_param_index, parent_step_id, step_status, step_status_code, num_children, num_children_completed, output_id, error_id, start_time, end_time, timezone_region, sequence_number, dispatcher_id) VALUES (:new.job_id, :new.execution_id, :new.step_id, :new.source_step_id, :new.original_step_id, :new.restart_mode, :new.step_name, :new.step_type, :new.command_type, :new.iterate_param, :new.iterate_param_index, :new.parent_step_id, :new.step_status, :new.step_status_code, :new.num_children, :new.num_children_completed, :new.output_id, :new.error_id, :new.start_time, :new.end_time, :new.timezone_region, :new.sequence_number, :new.dispatcher_id); END; / CREATE OR REPLACE TRIGGER mgmt_job_exec_update BEFORE UPDATE ON MGMT_JOB_EXECUTION FOR EACH ROW DECLARE --Audit l_audit_level NUMBER; BEGIN IF :new.end_time IS NOT NULL AND :new.end_time<:new.start_time THEN :new.end_time := :new.start_time; END IF; --Audit Job Step OutPut IF (:new.step_type = mgmt_job_engine.STEPTYPE_STEP) THEN mgmt_audit_admin.audit_level(l_audit_level); IF (l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_ALL OR l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_SELECTED) THEN mgmt_audit_log.update_job_step_info(:new.output_id, :new.step_id, :new.step_status); END IF; END IF; UPDATE MGMT_JOB_HISTORY SET job_id=:new.job_id, execution_id=:new.execution_id, step_id=:new.step_id, source_step_id=:new.source_step_id, original_step_id=:new.original_step_id, restart_mode=:new.restart_mode, step_name=:new.step_name, step_type=:new.step_type, command_type=:new.command_type, iterate_param=:new.iterate_param, iterate_param_index=:new.iterate_param_index, parent_step_id=:new.parent_step_id, step_status=:new.step_status, step_status_code=:new.step_status_code, num_children=:new.num_children, num_children_completed=:new.num_children_completed, output_id=:new.output_id, error_id=:new.error_id, start_time=:new.start_time, end_time=:new.end_time, sequence_number=:new.sequence_number, dispatcher_id=:new.dispatcher_id, oms_name=:new.oms_name WHERE step_id=:new.step_id; END; / show errors; rem rem rem Trigger JOB_CMD_BLK_DELETE_TRIGGER rem rem This trigger ensures that entries in the output table corresponding rem to the command block text are cleaned up when a row is deleted rem from the MGMT_JOB_STEP_COMMAND_LOG table rem CREATE OR REPLACE TRIGGER job_cmd_blk_delete_trigger BEFORE DELETE OR UPDATE OF command_block_text_id ON MGMT_JOB_STEP_COMMAND_LOG FOR EACH ROW DECLARE l_ref_count INTEGER; BEGIN UPDATE MGMT_JOB_OUTPUT set reference_count=reference_count-1 WHERE output_id=:old.command_block_text_id RETURNING reference_count INTO l_ref_count; IF l_ref_count=0 THEN DELETE FROM MGMT_JOB_OUTPUT WHERE output_id=:old.command_block_text_id; END IF; END; / show errors; rem rem Trigger JOB_EXECUTION_DELETE_TRIGGER rem rem This trigger ensures that entries in MGMT_JOB_OUTPUT rem are cleaned up when an entry is deleted from the MGMT_JOB_EXECUTION table rem rem CREATE OR REPLACE TRIGGER job_exec_delete_trigger AFTER DELETE ON MGMT_JOB_HISTORY FOR EACH ROW DECLARE l_ref_count INTEGER; BEGIN DELETE FROM MGMT_JOB_STEP_COMMAND_LOG WHERE step_id=:old.step_id; IF :old.output_id IS NOT NULL THEN UPDATE MGMT_JOB_OUTPUT set reference_count=reference_count-1 WHERE output_id=:old.output_id RETURNING reference_count INTO l_ref_count; IF l_ref_count = 0 THEN DELETE FROM MGMT_JOB_OUTPUT where output_id=:old.output_id; END IF; END IF; IF :old.error_id IS NOT NULL THEN UPDATE MGMT_JOB_OUTPUT set reference_count=reference_count-1 WHERE output_id=:old.error_id RETURNING reference_count INTO l_ref_count; IF l_ref_count = 0 THEN DELETE FROM MGMT_JOB_OUTPUT where output_id=:old.error_id; END IF; END IF; END; / rem rem Trigger JOB_EXECUTION_UPDATE_TRIGGER rem rem This trigger ensures that entries in MGMT_JOB_OUTPUT rem are cleaned up when an entry is updated in the MGMT_JOB_EXECUTION table rem rem CREATE OR REPLACE TRIGGER job_exec_update_trigger BEFORE UPDATE OF output_id, error_id ON MGMT_JOB_EXECUTION FOR EACH ROW DECLARE l_ref_count INTEGER; BEGIN IF :old.output_id IS NOT NULL AND :old.output_id != :new.output_id THEN UPDATE MGMT_JOB_OUTPUT set reference_count=reference_count-1 WHERE output_id=:old.output_id RETURNING reference_count INTO l_ref_count; IF l_ref_count = 0 THEN DELETE FROM MGMT_JOB_OUTPUT where output_id=:old.output_id; END IF; END IF; IF :old.error_id IS NOT NULL AND :old.error_id != :new.error_id THEN UPDATE MGMT_JOB_OUTPUT set reference_count=reference_count-1 WHERE output_id=:old.error_id RETURNING reference_count INTO l_ref_count; IF l_ref_count = 0 THEN DELETE FROM MGMT_JOB_OUTPUT where output_id=:old.error_id; END IF; END IF; END; / -- Insert trigger for large parameters -- Sets up a CLOB for a large parameter CREATE OR REPLACE TRIGGER mgmt_job_param_insert BEFORE INSERT ON MGMT_JOB_PARAMETER FOR EACH ROW DECLARE l_param_id MGMT_JOB_LARGE_PARAMS.param_id%TYPE; BEGIN IF :new.parameter_type = MGMT_JOBS.PARAM_TYPE_LARGE THEN IF :new.large_value IS NOT NULL THEN -- The output is already being referenced. Up the reference -- count UPDATE MGMT_JOB_LARGE_PARAMS SET reference_count=reference_count+1 WHERE param_id=:new.large_value; ELSE -- Insert a new clob for the parameter l_param_id := SYS_GUID(); INSERT INTO MGMT_JOB_LARGE_PARAMS(param_id, reference_count, param_value) VALUES (l_param_id, 1, empty_clob()); :new.large_value := l_param_id; END IF; END IF; END; / show errors; /** * Update large parameter reference count when the large_value is changed. This * happens only when an outer job passes large parameters to a nested job. */ CREATE OR REPLACE TRIGGER mgmt_job_param_update_large BEFORE UPDATE OF large_value ON MGMT_JOB_PARAMETER FOR EACH ROW BEGIN MGMT_JOB_ENGINE.decr_large_param_ref_count(:old.large_value, :new.large_value); END; / show errors; CREATE OR REPLACE TRIGGER mgmt_job_param_delete AFTER DELETE ON MGMT_JOB_PARAMETER FOR EACH ROW BEGIN IF :old.parameter_type = MGMT_JOBS.PARAM_TYPE_LARGE THEN MGMT_JOB_ENGINE.decr_large_param_ref_count(:old.large_value); END IF; END; / show errors; -- Insert trigger for large parameters in steps -- Sets up a CLOB for a large parameter CREATE OR REPLACE TRIGGER mgmt_step_param_insert BEFORE INSERT ON MGMT_JOB_STEP_PARAMS FOR EACH ROW DECLARE l_param_id MGMT_JOB_LARGE_PARAMS.param_id%TYPE; BEGIN IF :new.parameter_type = MGMT_JOBS.PARAM_TYPE_LARGE THEN IF :new.large_value IS NOT NULL THEN -- The output is already being referenced. Up the reference -- count UPDATE MGMT_JOB_LARGE_PARAMS SET reference_count=reference_count+1 WHERE param_id=:new.large_value; ELSE -- Insert a new clob for the parameter l_param_id := SYS_GUID(); INSERT INTO MGMT_JOB_LARGE_PARAMS(param_id, reference_count, param_value) VALUES (l_param_id, 1, empty_clob()); :new.large_value := l_param_id; END IF; END IF; END; / show errors; CREATE OR REPLACE TRIGGER mgmt_step_param_delete AFTER DELETE ON MGMT_JOB_STEP_PARAMS FOR EACH ROW BEGIN IF :old.parameter_type = MGMT_JOBS.PARAM_TYPE_LARGE THEN MGMT_JOB_ENGINE.decr_large_param_ref_count(:old.large_value); END IF; END; / show errors; -- Insert or update the most recent job type versions CREATE OR REPLACE TRIGGER job_update_recent_versions AFTER INSERT ON MGMT_JOB_TYPE_INFO FOR EACH ROW BEGIN INSERT INTO MGMT_JOB_TYPE_MAX_VERSIONS(job_type, job_type_id, major_version, minor_version1, minor_version2) VALUES (:new.job_type, :new.job_type_id, :new.major_version, :new.minor_version1, :new.minor_version2); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_JOB_TYPE_MAX_VERSIONS SET job_type_id=:new.job_type_id, minor_version1=:new.minor_version1, minor_version2=:new.minor_version2 WHERE job_type=:new.job_type AND major_version=:new.major_version; END; / -- Insert or update the most recent job type versions CREATE OR REPLACE TRIGGER job_cleanup_max_versions BEFORE DELETE ON MGMT_JOB_TYPE_INFO FOR EACH ROW DECLARE l_count NUMBER; l_job_names SMP_EMD_STRING_ARRAY; BEGIN MGMT_JOB_ENGINE.add_deleted_job_type(:old.job_type, :old.major_version, :old.job_type_id); SELECT count(1) INTO l_count FROM MGMT_JOB_EXEC_SUMMARY WHERE job_type_id=:old.job_type_id; IF l_count > 0 THEN SELECT DISTINCT job_name -- || '(status=' || status || ')' as job_name BULK COLLECT INTO l_job_names FROM MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY e WHERE j.job_id = e.job_id AND e.job_type_id=:old.job_type_id AND rownum < 10 ORDER BY job_name; raise_application_error(MGMT_GLOBAL.JOB_USING_JOBTYPE_DELETE_ERR, l_count || ' executions are currently using this version of the jobtype ' || :old.job_type || ' for jobs named ' || MGMT_JOB_UI.concat_string_array(l_job_names,', ') ); END IF; END; / CREATE OR REPLACE TRIGGER job_set_max_versions AFTER DELETE ON MGMT_JOB_TYPE_INFO BEGIN MGMT_JOB_ENGINE.update_max_versions; END; / -- Procedure to insert metadata about a credential source CREATE OR REPLACE TRIGGER job_insert_cred_type_data BEFORE INSERT ON MGMT_JOB_CRED_PARAMS FOR EACH ROW DECLARE l_cred_type_name MGMT_CREDENTIAL_TYPES.type_name%TYPE; l_type_meta_ver VARCHAR2(8); l_ref_meta_ver VARCHAR2(8); l_cred_target_type MGMT_TARGETS.target_type%TYPE; l_base_type_found BOOLEAN := false; l_cred_type_columns MGMT_JOB_VECTOR_PARAMS; l_ref_name MGMT_CREDENTIAL_TYPE_REF.ref_name%TYPE; l_ref_type_name MGMT_CREDENTIAL_TYPE_REF.ref_type_name%TYPE; l_ref_target_type MGMT_CREDENTIAL_TYPE_REF.ref_target_type%TYPE; BEGIN -- No processing for 4.0-style credentials IF :new.credential_set_name=MGMT_CREDENTIAL.CRED_SET_40_TARGET_USER OR :new.credential_set_name=MGMT_CREDENTIAL.CRED_SET_40_TARGET_SYSTEM OR :new.credential_set_name=MGMT_CREDENTIAL.CRED_SET_40_CONTAINER_USER OR :new.credential_set_name=MGMT_CREDENTIAL.CRED_SET_40_CONTAINER_SYSTEM THEN RETURN; END IF; -- Get the "base" credential type, as well as the target type IF INSTR(:new.credential_set_name, '%') = 1 THEN IF :new.base_cred_type_name IS NULL OR :new.base_cred_type_target_type IS NULL OR :new.base_cred_type_columns IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_CRED_SET_ERR, 'Credential type information MUST be specified for ' || ' variable credential sets'); ELSE -- The information has been provided already, so we're done RETURN; END IF; END IF; -- If the base credential info is already provided, then -- do not recompute IF :new.base_cred_type_name IS NOT NULL AND :new.base_cred_type_target_type IS NOT NULL AND :new.base_cred_type_columns IS NOT NULL THEN RETURN; ELSE :new.base_cred_type_name := null; :new.base_cred_type_target_type := null; :new.base_cred_type_columns := null; END IF; -- The credential set name is fixed; attempt to compute the base -- type l_cred_target_type := :new.credential_set_target_type; SELECT credential_type_name, target_type_meta_ver INTO l_cred_type_name, l_type_meta_ver FROM MGMT_CREDENTIAL_SETS WHERE set_name=:new.credential_set_name AND target_type=:new.credential_set_target_type AND ROWNUM=1; SELECT /*+ ORDERED */ type_column_name BULK COLLECT INTO l_cred_type_columns FROM MGMT_CREDENTIAL_SET_COLUMNS WHERE set_column_name IN (SELECT * FROM (TABLE(CAST(:new.credential_columns AS MGMT_JOB_VECTOR_PARAMS)))); WHILE NOT l_base_type_found LOOP BEGIN SELECT ref_name, ref_type_name, ref_target_type, ref_type_meta_ver INTO l_ref_name, l_ref_type_name, l_ref_target_type, l_ref_meta_ver FROM MGMT_CREDENTIAL_TYPE_REF WHERE target_type=l_cred_target_type AND target_type_meta_ver=l_type_meta_ver AND type_name=l_cred_type_name; -- Select the new credential type columns SELECT /*+ ORDERED */ ref_column_name BULK COLLECT INTO l_cred_type_columns FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_cred_target_type AND target_type_meta_ver=l_type_meta_ver AND type_name=l_cred_type_name AND ref_name=l_ref_name AND type_column_name IN (SELECT * FROM (TABLE(CAST(l_cred_type_columns AS MGMT_JOB_VECTOR_PARAMS)))); l_cred_type_name := l_ref_type_name; l_cred_target_type := l_ref_target_type; l_type_meta_ver := l_ref_meta_ver; EXCEPTION WHEN NO_DATA_FOUND THEN l_base_type_found := true; END; END LOOP; IF l_base_type_found THEN :new.base_cred_type_name := l_cred_type_name; :new.base_cred_type_target_type := l_cred_target_type; :new.base_cred_type_columns := l_cred_type_columns; END IF; END; / show errors; CREATE OR REPLACE TRIGGER MGMT_NESTED_JOB_CREDS_DEL AFTER DELETE ON MGMT_NESTED_JOB_CRED_INFO FOR EACH ROW BEGIN DELETE FROM MGMT_CREDENTIALS2 WHERE credential_guid=:old.credential_guid; END; / show errors; CREATE OR REPLACE TRIGGER job_state_chg_ins_trigger AFTER INSERT ON MGMT_JOB_STATE_CHANGES FOR EACH ROW DECLARE l_change MGMT_JOB_STATE_CHANGES%ROWTYPE; l_job_state_change SMP_EMD_NVPAIR_ARRAY; BEGIN l_change.state_change_guid := :new.state_change_guid; l_change.job_id := :new.job_id; l_change.execution_id := :new.execution_id; l_change.step_id := :new.step_id; l_change.logged := :new.logged; l_change.occurred := :new.logged; l_change.newstate := :new.newstate; l_change.status_bucket := :new.status_bucket; l_change.type := :new.type; l_change.violation_guid := :new.violation_guid; BEGIN l_job_state_change := SMP_EMD_NVPAIR_ARRAY (); l_job_state_change.extend (4); l_job_state_change(1) := SMP_EMD_NVPAIR ( MGMT_PROV_JOB_UTIL.PROV_JOB_ID, RAWTOHEX(:new.job_id ) ); l_job_state_change(2) := SMP_EMD_NVPAIR ( MGMT_PROV_JOB_UTIL.PROV_JOB_EXEC_ID, RAWTOHEX(:new.execution_id) ); l_job_state_change(3) := SMP_EMD_NVPAIR ( MGMT_PROV_JOB_UTIL.PROV_JOB_STATUS, TO_CHAR(:new.newstate) ); l_job_state_change(4) := SMP_EMD_NVPAIR ( MGMT_PROV_JOB_UTIL.PROV_JOB_STATUS_BUCKET, TO_CHAR(:new.status_bucket) ); MGMT_PROV_JOB_UTIL.UPDATE_PROV_JOB ( l_job_state_change ); EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(MGMT_JOB_ENGINE.MODULE_NAME, SQLCODE, 'Error in provisioning call back: ' ||SQLERRM || CHR(10) || 'JOB_ID:'||rawtohex(:new.job_id)); END; END; / show errors;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de