ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 触发器小总结

触发器小总结

原创 Linux操作系统 作者:iwillwill 时间:2012-05-14 15:08:25 0 删除 编辑

对于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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 简单触发器
请登录后发表评论 登录
全部评论

注册时间:2012-04-11

  • 博文量
    19
  • 访问量
    47196