修改oracle 触发器,Oracle 修改 新增 触发器 针对字段修改 触发器 误删Oracle表、数据、触发器找回 闪回查询...

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;