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;