REM REM Copyright (c) 2002, 2004, Oracle. All rights reserved. REM REM Name REM cwm2trg.sql REM REM Description REM Creates new cwm2$ triggers REM REM REM MODIFIED (MM/DD/YY) REM awesley 12/06/04 - merge delete triggers from 9.2.0.6.1 REM awesley 11/10/03 - adde trigger to olapsys.cwm$classification, olapsys.cwm$classificationentry REM dbardwel 09/23/03 - REM awesley 09/23/03 - 92041 to main REM cdalessi 06/19/03 - REM cdalessi 06/05/03 - cdalessi_txn107583 REM cdalessi 06/04/03 - REM cdalessi 05/01/03 - REM cdalessi 04/29/03 - rearrange for migration support REM cdalessi 04/28/03 - Creation REM create or replace trigger olapsys.CwM2$DimensionUPD before insert or update on olapsys.CwM2$Dimension for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$DimensionDEL before delete on olapsys.CwM2$Dimension for each row declare begin delete from olapsys.CwM2$AWDimLoad where Dim_IRID = :old.irid and Version_ID = 'CWM2'; delete from olapsys.CwM2$AWCompSpecMembership where IRID in (select csm.IRID from CwM2$AWCompositeSpec cs ,CwM2$AWCompSpecMembership csm where cs.IRID = csm.CompSpec_IRID and cs.Version_ID = 'CWM2' and csm.Dim_IRID = :old.irid); delete from olapsys.CwM2$AWCubeAggLevel where IRID in (select cal.IRID from CwM2$AWCubeAgg ca ,CwM2$AWCubeAggLevel cal where ca.IRID = cal.CubeAgg_IRID and ca.Version_ID = 'CWM2' and cal.Dim_IRID = :old.irid); end; / create or replace trigger olapsys.CwM$DimensionDEL before delete on olapsys.CWM$DIMENSION for each row declare begin delete from olapsys.CwM2$AWDimLoad where Dim_IRID = :old.irid and Version_ID = 'CWM'; delete from olapsys.CwM2$AWCompSpecMembership where IRID in (select csm.IRID from CwM2$AWCompositeSpec cs ,CwM2$AWCompSpecMembership csm where cs.IRID = csm.CompSpec_IRID and cs.Version_ID = 'CWM' and csm.Dim_IRID = :old.irid); delete from olapsys.CwM2$AWCubeAggLevel where IRID in (select cal.IRID from CwM2$AWCubeAgg ca ,CwM2$AWCubeAggLevel cal where ca.IRID = cal.CubeAgg_IRID and ca.Version_ID = 'CWM' and cal.Dim_IRID = :old.irid); end; / create or replace trigger olapsys.CwM2$LevelUPD before insert or update on olapsys.CwM2$Level for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$LevelDEL before delete on olapsys.CwM2$Level for each row declare begin delete from olapsys.CwM2$AWCubeAggLevel where IRID in (select cal.IRID from CwM2$AWCubeAgg ca ,CwM2$AWCubeAggLevel cal where ca.IRID = cal.CubeAgg_IRID and ca.Version_ID = 'CWM2' and cal.Level_IRID = :old.irid); end; / create or replace trigger olapsys.CwM$LevelDEL before delete on olapsys.CwM$Level for each row declare begin delete from olapsys.CwM2$AWCubeAggLevel where IRID in (select cal.IRID from CwM2$AWCubeAgg ca ,CwM2$AWCubeAggLevel cal where ca.IRID = cal.CubeAgg_IRID and ca.Version_ID = 'CWM' and cal.Level_IRID = :old.irid); end; / create or replace trigger olapsys.CwM2$HierarchyUPD before insert or update on olapsys.CwM2$Hierarchy for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$HierCustomSortUPD before insert or update on olapsys.CwM2$HierCustomSort for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$HierLevelRelUPD before insert or update on olapsys.CwM2$HierLevelRel for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$DimensionAttributeUPD before insert or update on olapsys.CwM2$DimensionAttribute for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$LevelAttributeUPD before insert or update on olapsys.CwM2$LevelAttribute for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$LevelAttributeMapUPD before insert or update on olapsys.CwM2$LevelAttributeMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$CubeUPD before insert or update on olapsys.CwM2$Cube for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$CubeDEL before delete on olapsys.CwM2$Cube for each row declare begin delete from olapsys.CwM2$AWCubeLoad where Cube_IRID = :old.irid and Version_ID = 'CWM2'; delete from olapsys.CwM2$AWCompositeSpec where Cube_IRID = :old.irid and Version_ID = 'CWM2'; delete from olapsys.CwM2$AWCubeAgg where Cube_IRID = :old.irid and Version_ID = 'CWM2'; end; / create or replace trigger olapsys.CwM$CubeDEL before delete on olapsys.CWM$CUBE for each row declare begin delete from olapsys.CwM2$AWCubeLoad where Cube_IRID = :old.irid and Version_ID = 'CWM'; delete from olapsys.CwM2$AWCompositeSpec where Cube_IRID = :old.irid and Version_ID = 'CWM'; delete from olapsys.CwM2$AWCubeAgg where Cube_IRID = :old.irid and Version_ID = 'CWM'; end; / create or replace trigger olapsys.CwM2$MeasureUPD before insert or update on olapsys.CwM2$Measure for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$MeasureDEL before delete on olapsys.CwM2$Measure for each row declare begin delete from olapsys.CwM2$AWCubeLoadMeasure where IRID in (select clm.IRID from CwM2$AWCubeLoad cl ,CwM2$AWCubeLoadMeasure clm where cl.IRID = clm.CubeLoad_IRID and cl.Version_ID = 'CWM2' and clm.Measure_IRID = :old.irid); end; / create or replace trigger olapsys.CwM$MeasureDEL before delete on olapsys.CwM$Measure for each row declare begin delete from olapsys.CwM2$AWCubeLoadMeasure where IRID in (select clm.IRID from CwM2$AWCubeLoad cl ,CwM2$AWCubeLoadMeasure clm where cl.IRID = clm.CubeLoad_IRID and cl.Version_ID = 'CWM' and clm.Measure_IRID = :old.irid); end; / create or replace trigger olapsys.CwM2$CubeDimensionUseUPD before insert or update on olapsys.CwM2$CubeDimensionUse for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$DimHierLvlMapUPD before insert or update on olapsys.CwM2$DimHierLvlMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$AW_DimensionMapUPD before insert or update on olapsys.CwM2$AW_DimensionMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$FactDimHierMapUPD before insert or update on olapsys.CwM2$FactDimHierMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$FactDimHierTplsDtlUPD before insert or update on olapsys.CwM2$FactDimHierTplsDtl for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$MeasureTableMapUPD before insert or update on olapsys.CwM2$MeasureTableMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$FactKeyDimHierMapUPD before insert or update on olapsys.CwM2$FactKeyDimHierMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$FactKeyDimHierLvlMapUPD before insert or update on olapsys.CwM2$FactKeyDimHierLvlMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$AW_MeasureMapUPD before insert or update on olapsys.CwM2$AW_MeasureMap for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$StoredDimLvlTplsUPD before insert or update on olapsys.CwM2$StoredDimLvlTpls for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$StoredDimLvlTplsDtlUPD before insert or update on olapsys.CwM2$StoredDimLvlTplsDtl for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$AWViewsUPD before insert or update on olapsys.CwM2$AWViews for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWDimLoadUPD before insert or update on olapsys.CWM2$AWDimLoad for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWDimLoadFilterUPD before insert or update on olapsys.CWM2$AWDimLoadFilter for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWDimLoadParmValueUPD before insert or update on olapsys.CWM2$AWDimLoadParmValue for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeLoadUPD before insert or update on olapsys.CWM2$AWCubeLoad for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeLoadFilterUPD before insert or update on olapsys.CWM2$AWCubeLoadFilter for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeLoadParmValueUPD before insert or update on olapsys.CWM2$AWCubeLoadParmValue for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeLoadMeasureUPD before insert or update on olapsys.CWM2$AWCubeLoadMeasure for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeLoadAggPlanUPD before insert or update on olapsys.CWM2$AWCubeLoadAggPlan for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeAggUPD before insert or update on olapsys.CWM2$AWCubeAgg for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeAggLevelUPD before insert or update on olapsys.CWM2$AWCubeAggLevel for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeAggMeasureUPD before insert or update on olapsys.CWM2$AWCubeAggMeasure for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCompositeSpecUPD before insert or update on olapsys.CWM2$AWCompositeSpec for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCompSpecMembershipUPD before insert or update on olapsys.CWM2$AWCompSpecMembership for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CWM2$AWCubeCompPlanUPD before insert or update on olapsys.CWM2$AWCubeCompPlan for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$AWViewColsUPD before insert or update on olapsys.CwM2$AWViewCols for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.CwM2$ClassificationVPUPD before insert or update on olapsys.CwM2$ClassificationValuePair for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedDate := SYSDATE; :new.UpdatedDate := null; else :new.UpdatedDate := SYSDATE; end if; :new.LastChangeUser := v_User_Name; end; / create or replace trigger olapsys.cwm$classificationUPD before insert or update on olapsys.cwm$classification for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedOn := SYSDATE; :new.CreatedBy := v_User_Name; else :new.LastModifiedOn := SYSDATE; :new.LastModifiedBy := v_User_Name; end if; end; / create or replace trigger olapsys.cwm$classificationentryUPD before insert or update on olapsys.cwm$classificationentry for each row declare v_User_Name varchar2(30); begin select USER into v_User_Name from dual; if inserting then :new.CreatedOn := SYSDATE; :new.CreatedBy := v_User_Name; else :new.LastModifiedOn := SYSDATE; :new.LastModifiedBy := v_User_Name; end if; end; /