Hi,
Below is DDL of master table and trigger source
temp(staging) table is same as master table but no constraints,index,partition and no trigger attached to it
CREATE TABLE "schema1"."Master1"
( "IDENTIFIER" VARCHAR2(255 BYTE),
"SERIAL" NUMBER(*,0),
"NODE" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"NODEALIAS" VARCHAR2(64 BYTE),
"MANAGER" VARCHAR2(64 BYTE),
"AGENT" VARCHAR2(64 BYTE),
"ALERTGROUP" VARCHAR2(255 BYTE),
"ALERTKEY" VARCHAR2(255 BYTE),
"SEVERITY" NUMBER(*,0),
"SUMMARY" VARCHAR2(255 BYTE),
"STATECHANGE" DATE,
"FIRSTOCCURRENCE" DATE,
"LASTOCCURRENCE" DATE,
"INTERNALLAST" DATE,
"POLL" NUMBER(*,0),
"TALLY" NUMBER(*,0),
"TYPE" NUMBER(*,0),
"CLASS" NUMBER(*,0),
"GRADE" NUMBER(*,0),
"LOCATION" VARCHAR2(64 BYTE),
"OWNERGID" NUMBER(*,0),
"OWNERUID" NUMBER(*,0),
"ACKNOWLEDGED" NUMBER(*,0),
"ACKNOWLEDGEDTIME" DATE,
"FLASH" NUMBER(*,0),
"EVENTID" VARCHAR2(255 BYTE),
"EXPIRETIME" NUMBER(*,0),
"SUPPRESSESCL" NUMBER(*,0),
"CUSTOMER" VARCHAR2(64 BYTE),
"SERVICE" VARCHAR2(256 BYTE),
"PHYSICALCARD" VARCHAR2(64 BYTE),
"PHYSICALPORT" NUMBER(*,0),
"PHYSICALSLOT" NUMBER(*,0),
"TASKLIST" NUMBER(*,0),
"LOCALNODEALIAS" VARCHAR2(64 BYTE),
"LOCALPRIOBJ" VARCHAR2(255 BYTE),
"LOCALROOTOBJ" VARCHAR2(255 BYTE),
"LOCALSECOBJ" VARCHAR2(255 BYTE),
"REMOTENODEALIAS" VARCHAR2(64 BYTE),
"REMOTEPRIOBJ" VARCHAR2(255 BYTE),
"REMOTEROOTOBJ" VARCHAR2(255 BYTE),
"REMOTESECOBJ" VARCHAR2(255 BYTE),
"X733CORRNOTIF" VARCHAR2(255 BYTE),
"X733EVENTTYPE" NUMBER(*,0),
"X733PROBABLECAUSE" NUMBER(*,0),
"X733SPECIFICPROB" VARCHAR2(64 BYTE),
"SERVERNAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"SERVERNAME_SERVERSERIAL" VARCHAR2(255 BYTE) NOT NULL ENABLE,
"SERVERSERIAL" NUMBER(*,0),
"URL" VARCHAR2(1024 BYTE),
"EXTENDEDATTR" VARCHAR2(4000 BYTE),
"PROBESUBSECONDID" NUMBER(*,0),
"COLLECTIONFIRST" DATE,
"AGGREGATIONFIRST" DATE,
"DISPLAYFIRST" DATE,
"ALARMDETAILS" VARCHAR2(255 BYTE),
"ASSIGNEDTIME" DATE,
"ESCALATEDTALLY" NUMBER(*,0),
"ESCALATEDTIME" DATE,
"IMPACTSTATUS" NUMBER(*,0),
"PURGEFLAG" NUMBER(*,0),
"ALARMPRIORITY" NUMBER(*,0),
"REPORTINGUSER" VARCHAR2(64 BYTE),
"POSSIBLEROOTCAUSE" NUMBER(*,0),
"SUPPRESSTIME" DATE,
"WRITETOHEDB" NUMBER(*,0),
"CUSTOMERID" NUMBER(*,0),
"CUSTOMERDETAILS" VARCHAR2(2000 BYTE),
"ALERTSTATUS" NUMBER(*,0),
"ALERTTYPE" NUMBER(*,0),
"MANAGEDOBJCLASS" VARCHAR2(64 BYTE),
"FLAPCOUNT" NUMBER(*,0),
"FLAPWINDOW" NUMBER(*,0),
"FLAPINDICATOR" NUMBER(*,0),
"GEOINFO" VARCHAR2(255 BYTE),
"INHAND" VARCHAR2(2 BYTE),
"INHANDEXPIRETIME" DATE,
"PARENTPOINTER" VARCHAR2(512 BYTE),
"PROCESSINGTIME" NUMBER(*,0),
"SOURCESERVERNAME" VARCHAR2(64 BYTE),
"SOURCESERVERSERIAL" NUMBER(*,0),
"SOURCESTATECHANGE" DATE,
"NOTIFICATIONFLAG" NUMBER(*,0),
"NOTIFICATIONGROUP" VARCHAR2(100 BYTE),
"NOTIFICATIONRULEID" VARCHAR2(100 BYTE),
"NOTIFICATIONTIME" DATE,
"SVCAGGEVENT" NUMBER(*,0),
"CLEARTALLY" NUMBER(*,0),
"CLEARTIMESTAMP" DATE,
"NEFIRSTTIME" DATE,
"NELASTTIME" DATE,
"NETIMEMETHOD" NUMBER(*,0),
"ORIGINALSEVERITY" NUMBER(*,0),
"ACFLAG" NUMBER(*,0),
"RCASTATUS" NUMBER(*,0),
"RCAPROCESSINGTIME" NUMBER(*,0),
"RCATALLY" NUMBER(*,0),
"RCATIMESTAMP" DATE,
"ROOTCAUSEDESC" VARCHAR2(256 BYTE),
"SERVICEID" VARCHAR2(64 BYTE),
"SIASTATUS" NUMBER(*,0),
"SERVICESIMPACTED" NUMBER(*,0),
"SERVICETYPE" VARCHAR2(64 BYTE),
"SIAPROCESSINGTIME" NUMBER(*,0),
"SIATALLY" NUMBER(*,0),
"SIATIMESTAMP" DATE,
"SERVICESTATUS" NUMBER(*,0),
"SERVICEAFFECTINGALARMS" VARCHAR2(1024 BYTE),
"COMMISSIONEDSTATE" VARCHAR2(64 BYTE),
"EQUIPMENTROLE" VARCHAR2(64 BYTE),
"EQUIPMENTTYPE" VARCHAR2(64 BYTE),
"EQUIPMENTKEY" VARCHAR2(255 BYTE),
"SRCDOMAIN" VARCHAR2(64 BYTE),
"SRCSUBDOMAIN" VARCHAR2(64 BYTE),
"SRCEMSIDENTIFIER" VARCHAR2(255 BYTE),
"TOPOLOGYKEY" VARCHAR2(255 BYTE),
"CORRELATETOPOLOGYKEY" VARCHAR2(255 BYTE),
"TTDISASSOCIATE" NUMBER(*,0),
"TTFLAG" NUMBER(*,0),
"TTPRIORITY" VARCHAR2(8 BYTE),
"TTPROBLEMDESC" VARCHAR2(4000 BYTE),
"TTREQUESTTIME" DATE,
"TTSEQUENCE" VARCHAR2(64 BYTE),
"TTSERVER" VARCHAR2(64 BYTE),
"TTSERVICEAFFECTED" VARCHAR2(64 BYTE),
"TTSLACOMMIT" DATE,
"TTSTATUS" VARCHAR2(64 BYTE),
"TTUPDATE" DATE,
"TTUSER" VARCHAR2(64 BYTE),
"TTWAITTIME" NUMBER(*,0),
"CAUSETYPE" NUMBER(*,0),
"ADVCORRSERVERNAME" VARCHAR2(64 BYTE),
"ADVCORRSERVERSERIAL" NUMBER(*,0),
"ADVCORRCAUSETYPE" NUMBER(*,0),
"LOCALTERTOBJ" VARCHAR2(255 BYTE),
"LOCALOBJRELATE" NUMBER(*,0),
"REMOTETERTOBJ" VARCHAR2(255 BYTE),
"REMOTEOBJRELATE" NUMBER(*,0),
"CORRSCORE" NUMBER(*,0),
"CUSTOMATTR1" VARCHAR2(1024 BYTE),
"CUSTOMATTR2" VARCHAR2(255 BYTE),
"CUSTOMATTR3" VARCHAR2(255 BYTE),
"CUSTOMATTR4" VARCHAR2(255 BYTE),
"CUSTOMATTR5" VARCHAR2(255 BYTE),
"DELETEDAT" DATE,
"CUSTOMATTR6" VARCHAR2(255 BYTE),
"CUSTOMATTR7" VARCHAR2(255 BYTE),
"CUSTOMATTR8" VARCHAR2(255 BYTE),
"CUSTOMATTR9" VARCHAR2(255 BYTE),
"CUSTOMATTR10" VARCHAR2(255 BYTE),
"CUSTOMATTR11" VARCHAR2(255 BYTE),
"CUSTOMATTR12" VARCHAR2(255 BYTE),
"CUSTOMATTR13" VARCHAR2(255 BYTE),
"CUSTOMATTR14" VARCHAR2(255 BYTE),
"CUSTOMATTR15" VARCHAR2(255 BYTE),
"CUSTOMATTR16" VARCHAR2(255 BYTE),
"CUSTOMATTR17" VARCHAR2(255 BYTE),
"CUSTOMATTR18" VARCHAR2(255 BYTE),
"CUSTOMATTR19" VARCHAR2(255 BYTE),
"CUSTOMATTR20" VARCHAR2(255 BYTE),
"CUSTOMATTR21" VARCHAR2(255 BYTE),
"CUSTOMATTR22" VARCHAR2(255 BYTE),
"CUSTOMATTR23" VARCHAR2(255 BYTE),
"CUSTOMATTR24" VARCHAR2(255 BYTE),
"CUSTOMATTR25" VARCHAR2(255 BYTE),
"CUSTOMATTR26" VARCHAR2(255 BYTE),
"CUSTOMATTR27" NUMBER(*,0),
"CUSTOMATTR28" NUMBER(*,0),
"CUSTOMATTR29" DATE,
"CUSTOMATTR30" DATE,
"CLEAREDBY" VARCHAR2(256 BYTE),
"OUTAGEDURATION" NUMBER(*,0),
"TTCI" VARCHAR2(256 BYTE),
"INVID" NUMBER(*,0),
"INVLOCATIONID" NUMBER(*,0),
"VENDORNAME" VARCHAR2(64 BYTE),
"FLUCTUATIONCATEGORY" NUMBER,
"INVPROTECTION" NUMBER(*,0),
"RCAPARENTHISTORY" VARCHAR2(1000 BYTE),
"RCAWEIGHTFACTOR" NUMBER(*,0),
PRIMARY KEY ("SERVERNAME", "SERVERSERIAL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_IDX" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT"
PARTITION BY RANGE ("LASTOCCURRENCE")
(PARTITION "STATUS_MDA_SEM_DAT_201709" VALUES LESS THAN (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201710" VALUES LESS THAN (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201711" VALUES LESS THAN (TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ,
PARTITION "STATUS_MDA_SEM_DAT_201712" VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_DAT" ) ENABLE ROW MOVEMENT ;
CREATE INDEX "schema1"."IX1_Master1" ON "schema1"."Master1" ("CLEARTIMESTAMP", "FIRSTOCCURRENCE", "TTSEQUENCE", "PARENTPOINTER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_SMALL" ;
CREATE INDEX "schema1"."IX_Master1" ON "schema1"."Master1" ("FIRSTOCCURRENCE", "ALERTGROUP", "CUSTOMATTR10", "EQUIPMENTKEY", "MANAGER", "CUSTOMATTR3", "AGENT", "NODE", "SEVERITY", "ORIGINALSEVERITY", "X733EVENTTYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_SMALL" ;
CREATE INDEX "schema1"."I_Master1" ON "schema1"."Master1" ("LASTOCCURRENCE", "CLASS", "NODE", "SERVERNAME_SERVERSERIAL", "SERIAL", "SRCSUBDOMAIN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "schema1_STAT_IDX" ;
CREATE OR REPLACE TRIGGER "schema1"."UPDATE_SIA_Master1" AFTER INSERT OR UPDATE ON Master1
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
DECLARE
cnt NUMBER(3,1);
BEGIN
IF INSERTING THEN
Begin
SELECT COUNT(SERVERSERIAL) INTO cnt from SIA_Master1 WHERE SERVERSERIAL = :old.SERVERSERIAL;
if (cnt = 0) then
if(:new.FIRSTOCCURRENCE > to_date('10/19/2017 12:45:00','MM/DD/YYYY HH24:MI:SS')) then
INSERT INTO SIA_Master1 (IDENTIFIER,SERIAL,NODE,NODEALIAS,MANAGER,AGENT,ALERTGROUP,ALERTKEY,SEVERITY,SUMMARY,STATECHANGE,FIRSTOCCURRENCE,LASTOCCURRENCE,INTER NALLAST,POLL,TALLY,TYPE,CLASS,GRADE,LOCATION,OWNERGID,OWNERUID,ACKNOWLEDGED,ACKNOWLEDGEDTIME,FLASH,EVENTID,EXPIRETIME,SUPPRESSESCL,CU STOMER,SERVICE,PHYSICALCARD,PHYSICALPORT,PHYSICALSLOT,TASKLIST,LOCALNODEALIAS,LOCALPRIOBJ,LOCALROOTOBJ,LOCALSECOBJ,REMOTENODEALIAS,RE MOTEPRIOBJ,REMOTEROOTOBJ,REMOTESECOBJ,X733CORRNOTIF,X733EVENTTYPE,X733PROBABLECAUSE,X733SPECIFICPROB,SERVERNAME,SERVERNAME_SERVERSERI AL,SERVERSERIAL,URL,EXTENDEDATTR,PROBESUBSECONDID,COLLECTIONFIRST,AGGREGATIONFIRST,DISPLAYFIRST,ALARMDETAILS,ASSIGNEDTIME,ESCALATEDTA LLY,ESCALATEDTIME,IMPACTSTATUS,PURGEFLAG,ALARMPRIORITY,REPORTINGUSER,POSSIBLEROOTCAUSE,SUPPRESSTIME,WRITETOHEDB,CUSTOMERID,CUSTOMERDE TAILS,ALERTSTATUS,ALERTTYPE,MANAGEDOBJCLASS,FLAPCOUNT,FLAPWINDOW,FLAPINDICATOR,GEOINFO,INHAND,INHANDEXPIRETIME,PARENTPOINTER,PROCESSI NGTIME,SOURCESERVERNAME,SOURCESERVERSERIAL,SOURCESTATECHANGE,NOTIFICATIONFLAG,NOTIFICATIONGROUP,NOTIFICATIONRULEID,NOTIFICATIONTIME,S VCAGGEVENT,CLEARTALLY,CLEARTIMESTAMP,NEFIRSTTIME,NELASTTIME,NETIMEMETHOD,ORIGINALSEVERITY,ACFLAG,RCASTATUS,RCAPROCESSINGTIME,RCATALLY ,RCATIMESTAMP,ROOTCAUSEDESC,SERVICEID,SIASTATUS,SERVICESIMPACTED,SERVICETYPE,SIAPROCESSINGTIME,SIATALLY,SIATIMESTAMP,SERVICESTATUS,SE RVICEAFFECTINGALARMS,COMMISSIONEDSTATE,EQUIPMENTROLE,EQUIPMENTTYPE,EQUIPMENTKEY,SRCDOMAIN,SRCSUBDOMAIN,SRCEMSIDENTIFIER,TOPOLOGYKEY,C ORRELATETOPOLOGYKEY,TTDISASSOCIATE,TTFLAG,TTPRIORITY,TTPROBLEMDESC,TTREQUESTTIME,TTSEQUENCE,TTSERVER,TTSERVICEAFFECTED,TTSLACOMMIT,TT STATUS,TTUPDATE,TTUSER,TTWAITTIME,CAUSETYPE,ADVCORRSERVERNAME,ADVCORRSERVERSERIAL,ADVCORRCAUSETYPE,LOCALTERTOBJ,LOCALOBJRELATE,REMOTE TERTOBJ,REMOTEOBJRELATE,CORRSCORE,CUSTOMATTR1,CUSTOMATTR2,CUSTOMATTR3,CUSTOMATTR4,CUSTOMATTR5,DELETEDAT,CUSTOMATTR6,CUSTOMATTR7,CUSTO MATTR8,CUSTOMATTR9,CUSTOMATTR10,CUSTOMATTR11,CUSTOMATTR12,CUSTOMATTR13,CUSTOMATTR14,CUSTOMATTR15,CUSTOMATTR16,CUSTOMATTR17,CUSTOMATTR 18,CUSTOMATTR19,CUSTOMATTR20,CUSTOMATTR21,CUSTOMATTR22,CUSTOMATTR23,CUSTOMATTR24,CUSTOMATTR25,CUSTOMATTR26,CUSTOMATTR27,CUSTOMATTR28, CUSTOMATTR29,CUSTOMATTR30,CLEAREDBY,OUTAGEDURATION,TTCI,INVID,INVLOCATIONID,VENDORNAME,FLUCTUATIONCATEGORY)
values (:new.IDENTIFIER,:new.SERIAL,:new.NODE,:new.NODEALIAS,:new.MANAGER,:new.AGENT,:new.ALERTGROUP,:new.ALERTKEY,:new.SEVERITY,:new.SUMMAR Y,:new.STATECHANGE,:new.FIRSTOCCURRENCE,:new.LASTOCCURRENCE,:new.INTERNALLAST,:new.POLL,:new.TALLY,:new.TYPE,:new.CLASS,:new.GRADE,:n ew.LOCATION,:new.OWNERGID,:new.OWNERUID,:new.ACKNOWLEDGED,:new.ACKNOWLEDGEDTIME,:new.FLASH,:new.EVENTID,:new.EXPIRETIME,:new.SUPPRESS ESCL,:new.CUSTOMER,:new.SERVICE,:new.PHYSICALCARD,:new.PHYSICALPORT,:new.PHYSICALSLOT,:new.TASKLIST,:new.LOCALNODEALIAS,:new.LOCALPRI OBJ,:new.LOCALROOTOBJ,:new.LOCALSECOBJ,:new.REMOTENODEALIAS,:new.REMOTEPRIOBJ,:new.REMOTEROOTOBJ,:new.REMOTESECOBJ,:new.X733CORRNOTIF ,:new.X733EVENTTYPE,:new.X733PROBABLECAUSE,:new.X733SPECIFICPROB,:new.SERVERNAME,:new.SERVERNAME_SERVERSERIAL,:new.SERVERSERIAL,:new. URL,:new.EXTENDEDATTR,:new.PROBESUBSECONDID,:new.COLLECTIONFIRST,:new.AGGREGATIONFIRST,:new.DISPLAYFIRST,:new.ALARMDETAILS,:new.ASSIG NEDTIME,:new.ESCALATEDTALLY,:new.ESCALATEDTIME,:new.IMPACTSTATUS,:new.PURGEFLAG,:new.ALARMPRIORITY,:new.REPORTINGUSER,:new.POSSIBLERO OTCAUSE,:new.SUPPRESSTIME,:new.WRITETOHEDB,:new.CUSTOMERID,:new.CUSTOMERDETAILS,:new.ALERTSTATUS,:new.ALERTTYPE,:new.MANAGEDOBJCLASS, :new.FLAPCOUNT,:new.FLAPWINDOW,:new.FLAPINDICATOR,:new.GEOINFO,:new.INHAND,:new.INHANDEXPIRETIME,:new.PARENTPOINTER,:new.PROCESSINGTI ME,:new.SOURCESERVERNAME,:new.SOURCESERVERSERIAL,:new.SOURCESTATECHANGE,:new.NOTIFICATIONFLAG,:new.NOTIFICATIONGROUP,:new.NOTIFICATIO NRULEID,:new.NOTIFICATIONTIME,:new.SVCAGGEVENT,:new.CLEARTALLY,:new.CLEARTIMESTAMP,:new.NEFIRSTTIME,:new.NELASTTIME,:new.NETIMEMETHOD ,:new.ORIGINALSEVERITY,:new.ACFLAG,:new.RCASTATUS,:new.RCAPROCESSINGTIME,:new.RCATALLY,:new.RCATIMESTAMP,:new.ROOTCAUSEDESC,:new.SERV ICEID,:new.SIASTATUS,:new.SERVICESIMPACTED,:new.SERVICETYPE,:new.SIAPROCESSINGTIME,:new.SIATALLY,:new.SIATIMESTAMP,:new.SERVICESTATUS ,:new.SERVICEAFFECTINGALARMS,:new.COMMISSIONEDSTATE,:new.EQUIPMENTROLE,:new.EQUIPMENTTYPE,:new.EQUIPMENTKEY,:new.SRCDOMAIN,:new.SRCSU BDOMAIN,:new.SRCEMSIDENTIFIER,:new.TOPOLOGYKEY,:new.CORRELATETOPOLOGYKEY,:new.TTDISASSOCIATE,:new.TTFLAG,:new.TTPRIORITY,:new.TTPROBL EMDESC,:new.TTREQUESTTIME,:new.TTSEQUENCE,:new.TTSERVER,:new.TTSERVICEAFFECTED,:new.TTSLACOMMIT,:new.TTSTATUS,:new.TTUPDATE,:new.TTUS ER,:new.TTWAITTIME,:new.CAUSETYPE,:new.ADVCORRSERVERNAME,:new.ADVCORRSERVERSERIAL,:new.ADVCORRCAUSETYPE,:new.LOCALTERTOBJ,:new.LOCALO BJRELATE,:new.REMOTETERTOBJ,:new.REMOTEOBJRELATE,:new.CORRSCORE,:new.CUSTOMATTR1,:new.CUSTOMATTR2,:new.CUSTOMATTR3,:new.CUSTOMATTR4,: new.CUSTOMATTR5,:new.DELETEDAT,:new.CUSTOMATTR6,:new.CUSTOMATTR7,:new.CUSTOMATTR8,:new.CUSTOMATTR9,:new.CUSTOMATTR10,:new.CUSTOMATTR1 1,:new.CUSTOMATTR12,:new.CUSTOMATTR13,:new.CUSTOMATTR14,:new.CUSTOMATTR15,:new.CUSTOMATTR16,:new.CUSTOMATTR17,:new.CUSTOMATTR18,:new. CUSTOMATTR19,:new.CUSTOMATTR20,:new.CUSTOMATTR21,:new.CUSTOMATTR22,:new.CUSTOMATTR23,:new.CUSTOMATTR24,:new.CUSTOMATTR25,:new.CUSTOMA TTR26,:new.CUSTOMATTR27,:new.CUSTOMATTR28,:new.CUSTOMATTR29,:new.CUSTOMATTR30,:new.CLEAREDBY,:new.OUTAGEDURATION,:new.TTCI,:new.INVID ,:new.INVLOCATIONID,:new.VENDORNAME,:new.FLUCTUATIONCATEGORY);
end if;
end if;
END;
END IF;
IF UPDATING('DELETEDAT')THEN
delete from SIA_Master1 where SERVERSERIAL = :old.SERVERSERIAL and :new.DELETEDAT is not null;
END IF;
IF UPDATING THEN
UPDATE SIA_Master1 a set
a.IDENTIFIER =:new.IDENTIFIER,a.SERIAL=:new.SERIAL,a.NODE=:new.NODE,a.NODEALIAS=:new.NODEALIAS,a.MANAGER=:new.MANAGER,a.AGENT=:new.AGENT,
a.ALERTGROUP=:new.ALERTGROUP,a.ALERTKEY=:new.ALERTKEY,a.SEVERITY=:new.SEVERITY,a.SUMMARY=:new.SUMMARY,a.STATECHANGE=:new.STATECHANGE, a.FIRSTOCCURRENCE=:new.FIRSTOCCURRENCE,
a.LASTOCCURRENCE=:new.LASTOCCURRENCE,a.INTERNALLAST=:new.INTERNALLAST,a.POLL=:new.POLL,a.TALLY=:new.TALLY,a.TYPE=:new.TYPE,a.CLASS=:n ew.CLASS,a.GRADE=:new.GRADE,a.LOCATION=:new.LOCATION,a.OWNERGID=:new.OWNERGID,a.OWNERUID=:new.OWNERUID,a.ACKNOWLEDGED=:new.ACKNOWLEDG ED,
a.ACKNOWLEDGEDTIME=:new.ACKNOWLEDGEDTIME,a.FLASH=:new.FLASH,a.EVENTID=:new.EVENTID,a.EXPIRETIME=:new.EXPIRETIME,a.SUPPRESSESCL=:new.S UPPRESSESCL,a.CUSTOMER=:new.CUSTOMER,a.SERVICE=:new.SERVICE,a.PHYSICALCARD=:new.PHYSICALCARD,a.PHYSICALPORT=:new.PHYSICALPORT,
a.PHYSICALSLOT=:new.PHYSICALSLOT,a.TASKLIST=:new.TASKLIST,a.LOCALNODEALIAS=:new.LOCALNODEALIAS,a.LOCALPRIOBJ=:new.LOCALPRIOBJ,a.LOCAL ROOTOBJ=:new.LOCALROOTOBJ,a.LOCALSECOBJ=:new.LOCALSECOBJ,a.REMOTENODEALIAS=:new.REMOTENODEALIAS,a.REMOTEPRIOBJ=:new.REMOTEPRIOBJ,
a.REMOTEROOTOBJ=:new.REMOTEROOTOBJ,a.REMOTESECOBJ=:new.REMOTESECOBJ,a.X733CORRNOTIF=:new.X733CORRNOTIF,a.X733EVENTTYPE=:new.X733EVENT TYPE,a.X733PROBABLECAUSE=:new.X733PROBABLECAUSE,a.X733SPECIFICPROB=:new.X733SPECIFICPROB,a.SERVERNAME=:new.SERVERNAME,a.SERVERNAME_SE RVERSERIAL=:new.SERVERNAME_SERVERSERIAL,a.SERVERSERIAL=:new.SERVERSERIAL,a.URL=:new.URL,a.EXTENDEDATTR=:new.EXTENDEDATTR,a.PROBESUBSE CONDID=:new.PROBESUBSECONDID,a.COLLECTIONFIRST=:new.COLLECTIONFIRST,a.AGGREGATIONFIRST=:new.AGGREGATIONFIRST,a.DISPLAYFIRST=:new.DISP LAYFIRST,a.ALARMDETAILS=:new.ALARMDETAILS,
a.ASSIGNEDTIME=:new.ASSIGNEDTIME,a.ESCALATEDTALLY=:new.ESCALATEDTALLY,a.ESCALATEDTIME=:new.ESCALATEDTIME,a.IMPACTSTATUS=:new.IMPACTST ATUS,a.PURGEFLAG=:new.PURGEFLAG,a.ALARMPRIORITY=:new.ALARMPRIORITY,a.REPORTINGUSER=:new.REPORTINGUSER,a.POSSIBLEROOTCAUSE=:new.POSSIB LEROOTCAUSE,a.SUPPRESSTIME=:new.SUPPRESSTIME,a.WRITETOHEDB=:new.WRITETOHEDB,a.CUSTOMERID=:new.CUSTOMERID,a.CUSTOMERDETAILS=:new.CUSTO MERDETAILS,a.ALERTSTATUS=:new.ALERTSTATUS,a.ALERTTYPE=:new.ALERTTYPE,a.MANAGEDOBJCLASS=:new.MANAGEDOBJCLASS,a.FLAPCOUNT=:new.FLAPCOUN T,a.FLAPWINDOW=:new.FLAPWINDOW,a.FLAPINDICATOR=:new.FLAPINDICATOR,a.GEOINFO=:new.GEOINFO,a.INHAND=:new.INHAND,a.INHANDEXPIRETIME=:new .INHANDEXPIRETIME,a.PARENTPOINTER=:new.PARENTPOINTER,
a.PROCESSINGTIME=:new.PROCESSINGTIME,a.SOURCESERVERNAME=:new.SOURCESERVERNAME,a.SOURCESERVERSERIAL=:new.SOURCESERVERSERIAL,a.SOURCEST ATECHANGE=:new.SOURCESTATECHANGE,a.NOTIFICATIONFLAG=:new.NOTIFICATIONFLAG,a.NOTIFICATIONGROUP=:new.NOTIFICATIONGROUP,a.NOTIFICATIONRU LEID=:new.NOTIFICATIONRULEID,a.NOTIFICATIONTIME=:new.NOTIFICATIONTIME,a.SVCAGGEVENT=:new.SVCAGGEVENT,a.CLEARTALLY=:new.CLEARTALLY,a.C LEARTIMESTAMP=:new.CLEARTIMESTAMP,a.NEFIRSTTIME=:new.NEFIRSTTIME,a.NELASTTIME=:new.NELASTTIME,a.NETIMEMETHOD=:new.NETIMEMETHOD,a.ORIG INALSEVERITY=:new.ORIGINALSEVERITY,a.ACFLAG=:new.ACFLAG,a.RCASTATUS=:new.RCASTATUS,a.RCAPROCESSINGTIME=:new.RCAPROCESSINGTIME,a.RCATA LLY=:new.RCATALLY,a.RCATIMESTAMP=:new.RCATIMESTAMP,a.ROOTCAUSEDESC=:new.ROOTCAUSEDESC,a.SERVICEID=:new.SERVICEID,a.SIASTATUS=:new.SIA STATUS,a.SERVICESIMPACTED=:new.SERVICESIMPACTED,a.SERVICETYPE=:new.SERVICETYPE,a.SIAPROCESSINGTIME=:new.SIAPROCESSINGTIME,a.SIATALLY= :new.SIATALLY,a.SIATIMESTAMP=:new.SIATIMESTAMP,a.SERVICESTATUS=:new.SERVICESTATUS,a.SERVICEAFFECTINGALARMS=:new.SERVICEAFFECTINGALARM S,a.COMMISSIONEDSTATE=:new.COMMISSIONEDSTATE,a.EQUIPMENTROLE=:new.EQUIPMENTROLE,a.EQUIPMENTTYPE=:new.EQUIPMENTTYPE,a.EQUIPMENTKEY=:ne w.EQUIPMENTKEY,a.SRCDOMAIN=:new.SRCDOMAIN,a.SRCSUBDOMAIN=:new.SRCSUBDOMAIN,
a.SRCEMSIDENTIFIER=:new.SRCEMSIDENTIFIER,a.TOPOLOGYKEY=:new.TOPOLOGYKEY,a.CORRELATETOPOLOGYKEY=:new.CORRELATETOPOLOGYKEY,a.TTDISASSOC IATE=:new.TTDISASSOCIATE,a.TTFLAG=:new.TTFLAG,a.TTPRIORITY=:new.TTPRIORITY,a.TTPROBLEMDESC=:new.TTPROBLEMDESC,a.TTREQUESTTIME=:new.TT REQUESTTIME,a.TTSEQUENCE=:new.TTSEQUENCE,a.TTSERVER=:new.TTSERVER,a.TTSERVICEAFFECTED=:new.TTSERVICEAFFECTED,a.TTSLACOMMIT=:new.TTSLA COMMIT,a.TTSTATUS=:new.TTSTATUS,a.TTUPDATE=:new.TTUPDATE,a.TTUSER=:new.TTUSER,a.TTWAITTIME=:new.TTWAITTIME,a.CAUSETYPE=:new.CAUSETYPE ,a.ADVCORRSERVERNAME=:new.ADVCORRSERVERNAME,a.ADVCORRSERVERSERIAL=:new.ADVCORRSERVERSERIAL,a.ADVCORRCAUSETYPE=:new.ADVCORRCAUSETYPE,a .LOCALTERTOBJ=:new.LOCALTERTOBJ,a.LOCALOBJRELATE=:new.LOCALOBJRELATE,a.REMOTETERTOBJ=:new.REMOTETERTOBJ,a.REMOTEOBJRELATE=:new.REMOTE OBJRELATE,a.CORRSCORE=:new.CORRSCORE,a.CUSTOMATTR1=:new.CUSTOMATTR1,a.CUSTOMATTR2=:new.CUSTOMATTR2,a.CUSTOMATTR3=:new.CUSTOMATTR3,a.C USTOMATTR4=:new.CUSTOMATTR4,a.CUSTOMATTR5=:new.CUSTOMATTR5,a.DELETEDAT=:new.DELETEDAT,a.CUSTOMATTR6=:new.CUSTOMATTR6,a.CUSTOMATTR7=:n ew.CUSTOMATTR7,a.CUSTOMATTR8=:new.CUSTOMATTR8,a.CUSTOMATTR9=:new.CUSTOMATTR9,a.CUSTOMATTR10=:new.CUSTOMATTR10,a.CUSTOMATTR11=:new.CUS TOMATTR11,a.CUSTOMATTR12=:new.CUSTOMATTR12,a.CUSTOMATTR13=:new.CUSTOMATTR13,a.CUSTOMATTR14=:new.CUSTOMATTR14,a.CUSTOMATTR15=:new.CUST OMATTR15,a.CUSTOMATTR16=:new.CUSTOMATTR16,a.CUSTOMATTR17=:new.CUSTOMATTR17,a.CUSTOMATTR18=:new.CUSTOMATTR18,a.CUSTOMATTR19=:new.CUSTO MATTR19,a.CUSTOMATTR20=:new.CUSTOMATTR20,a.CUSTOMATTR21=:new.CUSTOMATTR21,a.CUSTOMATTR22=:new.CUSTOMATTR22,a.CUSTOMATTR23=:new.CUSTOM ATTR23,a.CUSTOMATTR24=:new.CUSTOMATTR24,a.CUSTOMATTR25=:new.CUSTOMATTR25,a.CUSTOMATTR26=:new.CUSTOMATTR26,a.CUSTOMATTR27=:new.CUSTOMA TTR27,a.CUSTOMATTR28=:new.CUSTOMATTR28,a.CUSTOMATTR29=:new.CUSTOMATTR29,a.CUSTOMATTR30=:new.CUSTOMATTR30,a.CLEAREDBY=:new.CLEAREDBY,a .OUTAGEDURATION=:new.OUTAGEDURATION,a.TTCI=:new.TTCI,a.INVID=:new.INVID,a.INVLOCATIONID=:new.INVLOCATIONID,a.VENDORNAME=:new.VENDORNA ME,a.FLUCTUATIONCATEGORY=:new.FLUCTUATIONCATEGORY
WHERE a.SERVERSERIAL=:old.SERVERSERIAL;
END IF;
--IF DELETING THEN
--delete from SIA_Master1 sia where sia.IDENTIFIER = :old.IDENTIFIER;
--DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table');
END;
/
ALTER TRIGGER "schema1"."UPDATE_SIA_Master1" DISABLE;
|