Rem drv: <create type="triggers"/>
Rem
Rem $Header: stgAdv_triggers.sql 28-jun-2005.11:42:12 chyu Exp $
Rem
Rem stgAdv_triggers.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      stgAdv_triggers.sql - Storage Advisor triggers
Rem
Rem    DESCRIPTION
Rem      Creates the trigger(s) used by Storage Advisor 
Rem      a/k/a Space Advisor
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    rpinnama    05/11/04 - Use cerate or replace 
Rem    abodge      04/29/03 - abodge_add_settings_metrics
Rem    abodge      04/17/03 - Created
Rem

Rem SPACE_METRICS_PURGE_TRIGGER, along with view MGMT_SPACE_PURGE,
Rem purges records from the Space Advisor metrics table
Rem MGMT_SPACE_METRICS.  Inserting a row to this view purges
Rem records for the metric/target combination specified in
Rem the row.  Updating or deleting from the view are no-ops.

CREATE OR REPLACE TRIGGER space_metrics_purge_trigger 
  INSTEAD OF INSERT OR UPDATE OR DELETE ON mgmt_space_purge
  BEGIN
    IF(:old.target_guid IS NULL AND
       :new.target_guid IS NOT NULL) THEN
      DELETE FROM mgmt_space_metrics
      WHERE target_guid = :new.target_guid
        AND metric_name = :new.metric_name
        AND collection_timestamp < 
         (SELECT MAX(collection_timestamp)
          FROM mgmt_space_metrics sm
          WHERE sm.target_guid = :new.target_guid
            AND sm.metric_name = :new.metric_name);
    END IF;
  END;
/