Rem drv: <create type="triggers" pos="ip/ip_triggers.sql+"/>
Rem
Rem $Header: beacon_triggers.sql 12-jul-2005.13:50:47 andyao Exp $
Rem
Rem beacon_triggers.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_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    andyao      07/07/05 - fix composite key retrival 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
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    dcawley     09/14/04 - Remove calls to user model 
Rem    vjraghav    06/30/04 - Rel 2 changes 
Rem    snakai      07/16/02 - use target_guid accessing the comp. key table
Rem    dcawley     06/13/02 - Add insert and delete triggers on MGMT_BCN_TARGET
Rem    snakai      06/05/02 - snakai_del_website
Rem    snakai      06/04/02 - Created
Rem

Rem
Rem Generate the txn guid 
Rem
CREATE OR REPLACE TRIGGER bcn_txn_insert_trigger
   BEFORE INSERT ON MGMT_BCN_TXN_DEFN FOR EACH ROW
DECLARE
  v_txn_guid    RAW(16);
BEGIN

    IF (:new.target_guid IS NOT NULL) AND 
       (:new.name IS NOT NULL) THEN

      IF :new.txn_type IS NULL THEN
        :new.txn_type := 'HTTP';
      END IF;

      IF :new.txn_guid IS NULL THEN
        v_txn_guid := 
            dbms_obfuscation_toolkit.md5(
                   input => utl_raw.cast_to_raw(
                                   RAWTOHEX(:new.target_guid) ||
                                   ';' || :new.txn_type || 
                                   ';' || :new.name));
        :new.txn_guid := v_txn_guid;
      END IF;

    END IF;

END;
/
SHOW ERRORS;

Rem
Rem Generate the step guid 
Rem
CREATE OR REPLACE TRIGGER bcn_step_insert_trigger
   BEFORE INSERT ON MGMT_BCN_STEP_DEFN FOR EACH ROW
DECLARE
  v_step_guid    RAW(16);
BEGIN

    IF (:new.txn_guid IS NOT NULL) AND 
       (:new.name IS NOT NULL) THEN

      IF :new.step_type IS NULL THEN
        :new.step_type := 'HTTP';
      END IF;

      IF :new.step_guid IS NULL THEN
        v_step_guid := 
            dbms_obfuscation_toolkit.md5(
                   input => utl_raw.cast_to_raw(
                                   RAWTOHEX(:new.txn_guid) ||
                                   ';' || :new.step_type || 
                                   ';' || :new.name));
        :new.step_guid := v_step_guid;
      END IF;

    END IF;

END;
/
SHOW ERRORS;

Rem
Rem Generate the stepgroup guid 
Rem
CREATE OR REPLACE TRIGGER bcn_stepgroup_insert_trigger
   BEFORE INSERT ON MGMT_BCN_STEPGROUP_DEFN FOR EACH ROW
DECLARE
  v_stepgroup_guid    RAW(16);
BEGIN

    IF (:new.txn_guid IS NOT NULL) AND 
       (:new.name IS NOT NULL) THEN

      IF :new.stepgroup_guid IS NULL THEN
        v_stepgroup_guid := 
            dbms_obfuscation_toolkit.md5(
                   input => utl_raw.cast_to_raw(
                                   RAWTOHEX(:new.txn_guid) ||
                                   ';' || :new.stepgroup_type || 
                                   ';' || :new.name));
        :new.stepgroup_guid := v_stepgroup_guid;
      END IF;

    END IF;

END;
/
SHOW ERRORS;


Rem
Rem Delete the txn steps, groups which are stored in secondary
Rem tables, when the txn is deleted.
Rem
CREATE OR REPLACE TRIGGER bcn_txn_delete_trigger
    AFTER DELETE ON MGMT_BCN_TXN_DEFN FOR EACH ROW
DECLARE
    CURSOR key_cursor(tgt RAW, txn VARCHAR2) IS
    SELECT composite_key
    FROM MGMT_METRICS_COMPOSITE_KEYS
    WHERE target_guid = tgt
        AND key_part1_value = txn
        AND key_part3_value IS NULL;
        
    v_key key_cursor%ROWTYPE;
    v_more BOOLEAN;
BEGIN
    IF :old.txn_guid IS NOT NULL THEN
        DELETE FROM MGMT_BCN_STEP_DEFN
        WHERE target_guid = :old.target_guid
            AND txn_guid = :old.txn_guid;

        DELETE FROM MGMT_BCN_STEPGROUP_DEFN
        WHERE target_guid = :old.target_guid
            AND txn_guid = :old.txn_guid;

        DELETE FROM MGMT_BCN_TXN_PROPS
        WHERE target_guid = :old.target_guid
            AND txn_guid = :old.txn_guid;

        DELETE FROM MGMT_BCN_BCNTXN_PROPS
        WHERE target_guid = :old.target_guid
            AND txn_guid = :old.txn_guid;

        DELETE FROM MGMT_ADMIN_METRIC_THRESHOLDS
         WHERE target_guid = :old.target_guid
           AND coll_name = :old.txn_guid;
            
        v_more := TRUE;
        OPEN key_cursor(:old.target_guid, :old.name);
        WHILE v_more LOOP
            FETCH key_cursor INTO v_key;
            v_more := key_cursor%FOUND;
            IF v_more = TRUE THEN
                IF v_key.composite_key IS NOT NULL THEN
                    DELETE FROM MGMT_CURRENT_METRICS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                    DELETE FROM MGMT_METRIC_THRESHOLDS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                END IF;
            END IF;
        END LOOP;
        CLOSE key_cursor;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF key_cursor%ISOPEN THEN
            CLOSE key_cursor;
        END IF;
    RAISE;
END;
/
SHOW ERRORS;

Rem
Rem Delete all entries that are related to a step in a transaction
Rem

CREATE OR REPLACE TRIGGER bcn_step_delete_trigger
    AFTER DELETE ON MGMT_BCN_STEP_DEFN FOR EACH ROW
DECLARE
    CURSOR key_cursor(tgt_id RAW, txn_id RAW, step VARCHAR2) IS
    SELECT composite_key
    FROM MGMT_METRICS_COMPOSITE_KEYS
    WHERE target_guid = tgt_id
        AND key_part1_value = txn_id
        AND key_part3_value = step;

    v_key key_cursor%ROWTYPE;

    v_more BOOLEAN;
BEGIN
    IF :old.step_guid IS NOT NULL THEN
        DELETE FROM MGMT_BCN_STEP_PROPS
        WHERE target_guid = :old.target_guid
            AND step_guid = :old.step_guid;

        DELETE FROM MGMT_BCN_BCNSTEP_PROPS
        WHERE target_guid = :old.target_guid
            AND step_guid = :old.step_guid;

        DELETE FROM MGMT_BCN_STEPGROUP_STEPS
        WHERE target_guid = :old.target_guid
            AND step_guid = :old.step_guid;

        DELETE FROM MGMT_ADMIN_METRIC_THRESHOLDS
         WHERE target_guid = :old.target_guid
           AND coll_name = :old.step_guid;
 
        v_more := TRUE;
        OPEN key_cursor(:old.target_guid, :old.txn_guid, :old.name);
        WHILE v_more LOOP
            FETCH key_cursor INTO v_key;
            v_more := key_cursor%FOUND;
            IF v_more = TRUE THEN
                IF v_key.composite_key IS NOT NULL THEN
                    DELETE FROM MGMT_CURRENT_METRICS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                    DELETE FROM MGMT_METRIC_THRESHOLDS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                END IF;
            END IF;
        END LOOP;
        CLOSE key_cursor;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF key_cursor%ISOPEN THEN
            CLOSE key_cursor;
        END IF;
    RAISE;
END;
/
SHOW ERRORS;

Rem
Rem Delete all entries that are related to a stepgroup in a transaction
Rem

CREATE OR REPLACE TRIGGER bcn_stepgroup_delete_trigger
    AFTER DELETE ON MGMT_BCN_STEPGROUP_DEFN FOR EACH ROW
DECLARE
    CURSOR key_cursor(tgt_id RAW, txn_id RAW, stepgroup VARCHAR2) IS
    SELECT composite_key
    FROM MGMT_METRICS_COMPOSITE_KEYS
    WHERE target_guid = tgt_id
        AND key_part1_value = txn_id
        AND key_part3_value = stepgroup;

    v_key key_cursor%ROWTYPE;

    v_more BOOLEAN;
BEGIN
    IF :old.stepgroup_guid IS NOT NULL THEN
        DELETE FROM MGMT_BCN_STEPGROUP_STEPS
        WHERE target_guid = :old.target_guid
            AND stepgroup_guid = :old.stepgroup_guid;

        DELETE FROM MGMT_ADMIN_METRIC_THRESHOLDS
         WHERE target_guid = :old.target_guid
           AND coll_name = :old.stepgroup_guid;
 
        v_more := TRUE;
        OPEN key_cursor(:old.target_guid, :old.txn_guid, :old.name);
        WHILE v_more LOOP
            FETCH key_cursor INTO v_key;
            v_more := key_cursor%FOUND;
            IF v_more = TRUE THEN
                IF v_key.composite_key IS NOT NULL THEN
                    DELETE FROM MGMT_CURRENT_METRICS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                    DELETE FROM MGMT_METRIC_THRESHOLDS
                    WHERE target_guid = :old.target_guid
                        AND key_value = v_key.composite_key;
                        
                END IF;
            END IF;
        END LOOP;
        CLOSE key_cursor;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF key_cursor%ISOPEN THEN
            CLOSE key_cursor;
        END IF;
    RAISE;
END;
/
SHOW ERRORS;