ITPub博客

首页 > 数据库 > Oracle > 通过instead of触发器更新视图的一个bug

通过instead of触发器更新视图的一个bug

原创 Oracle 作者:abstractcyj 时间:2015-08-21 21:02:41 0 删除 编辑
视图代码:
CREATE OR REPLACE VIEW V_SALE_SETTINGS AS
SELECT b.compid,
       b.busno,
       b.orgname,
       b.orgshortname,
       b.orgabc,
       nvl(t.dt, dd.accdate) accdate,
       nvl(dd.amt_cash, 0) amt_cash,
       nvl(dd.amt_ybk, 0) amt_ybk,
       nvl(dd.amt_ylk, 0) amt_ylk,
       nvl(dd.amt_tzk, 0) amt_tzk,
       nvl(dd.amt_csk, 0) amt_csk,
       nvl(dd.amt_cost, 0) amt_cost,
       nvl(dd.status, 0) status,
       nvl(dd.gen_flag, 0) gen_flag,
       dd.errmsg
  FROM s_busi b
  FULL JOIN (SELECT trunc(to_date('20150801', 'yyyymmdd') + LEVEL) dt
               FROM dual
             CONNECT BY trunc(to_date('20150801', 'yyyymmdd') + LEVEL) <=
                        to_date('20250101', 'yyyymmdd')) t
    ON 1 = 1
  LEFT JOIN dd_sale_gen_set dd
    ON b.busno = dd.busno
   AND dd.accdate = t.dt;

注:这么写是因为前端处理比较方便

CREATE TRIGGER tr_v_sale_settings
  INSTEAD OF UPDATE OR INSERT ON v_sale_settings
  FOR EACH ROW
BEGIN
  --dbms_output.put_line(:new.busno);
  MERGE INTO dd_sale_gen_set dd
  USING (SELECT :new.busno    busno,
                :new.accdate  accdate,
                :new.amt_cash amt_cash,
                :new.amt_ybk  amt_ybk,
                :new.amt_ylk  amt_ylk,
                :new.amt_tzk  amt_tzk,
                :new.amt_csk  amt_csk,
                :new.amt_cost amt_cost,
                :new.status   status,
                :new.gen_flag gen_flag,
                :new.errmsg   errmsg
           FROM dual) t
  ON (dd.busno = t.busno AND dd.accdate = t.accdate)
  WHEN MATCHED THEN
    UPDATE
       SET dd.amt_cash = :new.amt_cash,
           dd.amt_ybk  = :new.amt_ybk,
           dd.amt_ylk  = :new.amt_ylk,
           dd.amt_tzk  = :new.amt_tzk,
           dd.amt_csk  = :new.amt_csk,
           dd.amt_cost = :new.amt_cost,
           dd.status   = :new.status,
           dd.gen_flag = :new.gen_flag,
           dd.errmsg   = :new.errmsg
  WHEN NOT MATCHED THEN
    INSERT
      (busno,
       accdate,
       amt_cash,
       amt_ybk,
       amt_ylk,
       amt_tzk,
       amt_csk,
       amt_cost,
       status,
       gen_flag,
       errmsg)
    VALUES
      (t.busno,
       t.accdate,
       t.amt_cash,
       t.amt_ybk,
       t.amt_ylk,
       t.amt_tzk,
       t.amt_csk,
       t.amt_cost,
       t.status,
       t.gen_flag,
       t.errmsg);
END v_sale_settings;

写一个这样的更新语句:update v_sale_settings set errmsg = 'A' WHERE rownum = 1
此时更新语句提示错误:ORA-01031

百思不得其解,苦思不得解。
后来,在给这个视图授权的时候,出现了错误, ORA-01720

于是想会不会是视图中的DUAL导致权限不足的错误
将中间带有DUAL的部分也创建为一个视图,再尝试去更新,更新成功。

create view v_sale_accdt as SELECT trunc(to_date('20150801', 'yyyymmdd') + LEVEL) dt
               FROM dual
             CONNECT BY trunc(to_date('20150801', 'yyyymmdd') + LEVEL) <=
                        to_date('20250101', 'yyyymmdd')


CREATE OR REPLACE VIEW V_SALE_SETTINGS AS
SELECT b.compid,
       b.busno,
       b.orgname,
       b.orgshortname,
       b.orgabc,
       nvl(t.dt, dd.accdate) accdate,
       nvl(dd.amt_cash, 0) amt_cash,
       nvl(dd.amt_ybk, 0) amt_ybk,
       nvl(dd.amt_ylk, 0) amt_ylk,
       nvl(dd.amt_tzk, 0) amt_tzk,
       nvl(dd.amt_csk, 0) amt_csk,
       nvl(dd.amt_cost, 0) amt_cost,
       nvl(dd.status, 0) status,
       nvl(dd.gen_flag, 0) gen_flag,
       dd.errmsg
  FROM s_busi b
  FULL JOIN v_sale_accdt t
    ON 1 = 1
  LEFT JOIN dd_sale_gen_set dd
    ON b.busno = dd.busno
   AND dd.accdate = t.dt;




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

请登录后发表评论 登录
全部评论
曾从事java方向开发多年。近年已经转入数据库方向。主要擅长SQL优化,Oracle数据库问题诊断,Oracle备份与恢复等。服务于医药物流,医院等行业

注册时间:2010-01-26

  • 博文量
    555
  • 访问量
    833589