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

emmmm 写这个博客心情很复杂，，，本来这个触发器早就写好了，后来发生点事就写个博客当个备份吧，就当留纪念了；话不多数上问题以及SQL：

 

问题：

　　

1. 在ABONPB表上增加一个触发器，针对车牌号字段做update操作且new:传入的mduser字段为API，status为P时，向PA_BANK_REGNO_RECORD表中insert 一条记录，sync字段为0，修改次数为之前的次数加1，其余字段参考字段意义。（YC）
2. 当insert进ABONPB时新案件时，同理lla_code为MA  为条件也要触发上班触发器，计数加一（后来添加）

 更新：提交一个闪回查询找误删数据的SQL，类似的网上还有很多就不一一举例了；；；；；

　　闪回查询：：

select * from dba_source a where a.type='TRIGGER';
SELECT * from dba_source a where a.name LIKE  '%ABONPB_%';



SELECT NAME, TEXT
      FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2018-06-05 12:20:00', 'yyyy-mm-dd hh24:mi:ss')
      WHERE OWNER = 'ACSPRD'
      AND TYPE = 'PROCEDURE'
      AND NAME LIKE  '%ABONPB_UPDATE%';

 

 

触发器解决：

 

CREATE OR REPLACE TRIGGER ABONPB_UPDATE_20180615
  BEFORE UPDATE OR INSERT ON ABONPB_20180615
  FOR EACH ROW
DECLARE
  IXEST             INTEGER;
  CHAGE             INTEGER;
  IXEST2             INTEGER;
  CHAGE2             INTEGER;
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 INTO EMAPIII.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;

      UPDATE EMAPIII.PA_BANK_REGNO_RECORD
         SET REGNO =:new.Regnr, CHANGE_TIMES = CHAGE+1 , CHANGE_TIME = SYSDATE
       WHERE IDCARD_NO = :OLD.POLICYNR
         AND CARD_NO = :OLD.VIN
         AND 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 INTO EMAPIII.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;

      UPDATE EMAPIII.PA_BANK_REGNO_RECORD
         SET REGNO =:new.Regnr, CHANGE_TIMES = CHAGE2+1 , CHANGE_TIME = SYSDATE
       WHERE IDCARD_NO = :new.POLICYNR
         AND CARD_NO = :new.VIN
         AND LASTNAME = :new.LASTNAME;

    END IF;
  
    END IF;
END;

 

ps：最后的最后一定要注意！！！！！！删除表时一定要备份该表的触发器、索引等，，，，，说多了都是泪啊！！！！！再然后，误删了没备份可以试试Oracle的闪回查询，运气好的话可以找回，，，

 

以上！！！

 

转载于:https://www.cnblogs.com/yangchengdebokeyuan/p/9240740.html