触发器小总结

对于oracle行级触发器(for each row)，不能对本表做任何操作，包括读取
原则：
在before insert触发器中，可以实现对本表的访问；
在after insert触发器中，不能实现对本表的访问；
在before/after update/delete触发器中，都不能实现对本表的访问

还得使用自治事务，加Commit;

在触发器中Role权限无效，需要手动给用户分配权限，否则会报权限不够或是找不到表或视图的错误。

 

CREATE OR REPLACE TRIGGER JYTB_G380
  BEFORE UPDATE OR INSERT OR DELETE ON DB002_OWNER.KWNBY_01000033_0007
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  CASE
    WHEN UPDATING THEN
      DELETE FROM DB005_OWNER.KWNBY_00309999_JY_0010
       WHERE DATE_YEAR = :OLD.DATE_YEAR
         AND DWZD_BH = :OLD.DWZD_BH
         AND SJKJ_XMBH1 = :OLD.SJKJ_XMBH1
         AND SJKJ_XMBH2 = :OLD.SJKJ_XMBH2
         AND TJZB_BH = :OLD.TJZB_BH;

      COMMIT;

      INSERT INTO DB005_OWNER.KWNBY_00309999_JY_0010
        (DATE_DATE,
         DATE_YEAR,
         DWZD_BH,
         YSBB_BH,
         ZBFL_BH,
         TJZB_BH,
         SJKJ_BH,
         SJKJ_XMBH1,
         SJKJ_XMBH2,
         ZBSJ_DATA0)
      VALUES
        (:NEW.DATE_DATE,
         :NEW.DATE_YEAR,
         :NEW.DWZD_BH,
         :NEW.YSBB_BH,
         '00309999',
         :NEW.TJZB_BH,
         'JY_0010',
         :NEW.SJKJ_XMBH1,
         :NEW.SJKJ_XMBH2,
         :NEW.ZBSJ_DATA2);
      COMMIT;
    WHEN INSERTING THEN
      DELETE FROM DB005_OWNER.KWNBY_00309999_JY_0010
       WHERE DATE_YEAR = :NEW.DATE_YEAR
         AND DWZD_BH = :NEW.DWZD_BH
         AND SJKJ_XMBH1 = :NEW.SJKJ_XMBH1
         AND SJKJ_XMBH2 = :NEW.SJKJ_XMBH2
         AND TJZB_BH = :OLD.TJZB_BH;
      COMMIT;
      INSERT INTO DB005_OWNER.KWNBY_00309999_JY_0010
        (DATE_DATE,
         DATE_YEAR,
         DWZD_BH,
         YSBB_BH,
         ZBFL_BH,
         TJZB_BH,
         SJKJ_BH,
         SJKJ_XMBH1,
         SJKJ_XMBH2,
         ZBSJ_DATA0)
      VALUES
        (:NEW.DATE_DATE,
         :NEW.DATE_YEAR,
         :NEW.DWZD_BH,
         :NEW.YSBB_BH,
         '00309999',
         :NEW.TJZB_BH,
         'JY_0010',
         :NEW.SJKJ_XMBH1,
         :NEW.SJKJ_XMBH2,
         :NEW.ZBSJ_DATA2);
      COMMIT;
    WHEN DELETING THEN
      DELETE FROM DB005_OWNER.KWNBY_00309999_JY_0010
       WHERE DATE_YEAR = :OLD.DATE_YEAR
         AND DWZD_BH = :OLD.DWZD_BH
         AND SJKJ_XMBH1 = :OLD.SJKJ_XMBH1
         AND SJKJ_XMBH2 = :OLD.SJKJ_XMBH2;
      COMMIT;

  END CASE;

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26869973/viewspace-723524/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26869973/viewspace-723524/