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

最新推荐文章于 2024-05-27 23:15:56 发布

congsong2560

最新推荐文章于 2024-05-27 23:15:56 发布

阅读量139

点赞数

文章标签：数据库

本文详细介绍了如何通过创建视图绕过权限限制，解决Oracle数据库中视图与触发器操作中出现的ORA-01720错误。通过将复杂查询分解为简单视图，避免了直接在视图中使用DUAL导致的权限不足问题，最终实现了成功更新视图数据的目标。

摘要由CSDN通过智能技术生成

视图代码：
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;