CREATE OR REPLACE TRIGGERABONPB_UPDATE_20180615
BEFOREUPDATE OR INSERT ONABONPB_20180615FOREACH ROWDECLAREIXESTINTEGER;
CHAGEINTEGER;
IXEST2INTEGER;
CHAGE2INTEGER;BEGIN
IF updating('regnr') AND :OLD.MDUSR = 'API' AND :OLD.STATUS = 'P'AND :old.Regnr != :new.Regnr THEN
SELECT COUNT(1) INTO IXEST FROM EMAPIII.PA_BANK_REGNO_RECORD T WHERE T.IDCARD_NO = :OLD.POLICYNR AND T.CARD_NO = :OLD.VIN AND T.LASTNAME =:OLD.LASTNAME;IF IXEST = 0 THEN
INSERT INTOEMAPIII.PA_BANK_REGNO_RECORD
(ID,
IDCARD_NO,
CARD_NO,
LASTNAME,
REGNO,
CHANGE_TIMES,
CHANGE_TIME,
CONTRACTCODE,
SERIE,
SYNC,
ACCTNO)VALUES(EMAPIII.PA_BANK_REGNO_RECORD_SEQ.NEXTVAL,
:NEW.VIN,
:NEW.POLICYNR,
:NEW.LASTNAME,
:NEW.REGNR,0,
SYSDATE,
:NEW.LCO_LNA_CODE,
:NEW.SERIE,0,
:NEW.SUBNUMBER);ELSE
SELECT T.CHANGE_TIMES INTO CHAGE FROM EMAPIII.PA_BANK_REGNO_RECORD T WHERE T.IDCARD_NO = :OLD.POLICYNR AND T.CARD_NO = :OLD.VIN AND T.LASTNAME =:OLD.LASTNAME;UPDATEEMAPIII.PA_BANK_REGNO_RECORDSET REGNO =:new.Regnr, CHANGE_TIMES = CHAGE+1 , CHANGE_TIME =SYSDATEWHERE IDCARD_NO =:OLD.POLICYNRAND CARD_NO =:OLD.VINAND LASTNAME =:OLD.LASTNAME;END IF;END IF;IF :new.Lla_Code='MA'THEN
SELECT COUNT(1) INTO IXEST2 FROM EMAPIII.PA_BANK_REGNO_RECORD T WHERE T.IDCARD_NO = :new.POLICYNR AND T.CARD_NO = :new.VIN AND T.LASTNAME =:new.LASTNAME;IF IXEST2 = 0 THEN
INSERT INTOEMAPIII.PA_BANK_REGNO_RECORD
(ID,
IDCARD_NO,
CARD_NO,
LASTNAME,
REGNO,
CHANGE_TIMES,
CHANGE_TIME,
CONTRACTCODE,
SERIE,
SYNC,
ACCTNO)VALUES(EMAPIII.PA_BANK_REGNO_RECORD_SEQ.NEXTVAL,
:NEW.VIN,
:NEW.POLICYNR,
:NEW.LASTNAME,
:NEW.REGNR,0,
SYSDATE,
:NEW.LCO_LNA_CODE,
:NEW.SERIE,0,
:NEW.SUBNUMBER);ELSE
SELECT T.CHANGE_TIMES INTO CHAGE2 FROM EMAPIII.PA_BANK_REGNO_RECORD T WHERE T.IDCARD_NO = :new.POLICYNR AND T.CARD_NO = :new.VIN AND T.LASTNAME =:new.LASTNAME;UPDATEEMAPIII.PA_BANK_REGNO_RECORDSET REGNO =:new.Regnr, CHANGE_TIMES = CHAGE2+1 , CHANGE_TIME =SYSDATEWHERE IDCARD_NO =:new.POLICYNRAND CARD_NO =:new.VINAND LASTNAME =:new.LASTNAME;END IF;END IF;END;