时间戳

首页 > 解决方案 > Oracle (v18/19) 实体化视图上的触发器不知道旧值

database - Oracle (v18/19) 实体化视图上的触发器不知道旧值

问题描述

在我们的工具中，我们在物化视图上使用触发器，以便在提交事务时创建日志条目（并执行一些其他操作）。该代码在 Oracle 12 中运行良好。在 Oracle 19 中，该触发器中的旧值 (":old") 似乎丢失了。

调查： 这似乎是物化视图/触发器组合的情况。如果我们在表上设置相同的触发器，则会正确生成日志（但我们没有获得所需的事务感知）。

我创建了一个 MWE 并向 DBMS_OUTPUT-Lines 添加了注释，这些注释描述了我们在 oracle 12 和 Oracle 18/19 中看到的内容：

/*Create Test-Table*/
CREATE TABLE MAT_VIEW_TEST (
    PK number(10,0) PRIMARY KEY ,
    NAME NVARCHAR2(50)
);

/*insert some values*/
insert into MAT_VIEW_TEST values (1, 'Herbert');
insert into MAT_VIEW_TEST values (2, 'Hubert');
commit;

/*Create mateterialized view (log) in order to set trigger on it*/
CREATE MATERIALIZED VIEW LOG ON MAT_VIEW_TEST WITH PRIMARY KEY, ROWID including new values;

CREATE MATERIALIZED VIEW MV_MAT_VIEW_TEST
    refresh fast on commit
AS select * from MAT_VIEW_TEST;

/*Create trigger to log old and new value*/
CREATE OR REPLACE TRIGGER MAT_VIEW_TRIGGER
    BEFORE INSERT OR UPDATE
    ON MV_MAT_VIEW_TEST
    FOR EACH ROW
DECLARE
    old_pk number(10,0);
    new_pk number(10,0);
    old_name NVARCHAR2(50);
    new_name NVARCHAR2(50);
BEGIN

    old_pk := :old.pk;
    old_name := :old.name;
    new_pk := :new.pk;
    new_name := :new.name;

    DBMS_OUTPUT.PUT_LINE('TEST BEGIN');
    DBMS_OUTPUT.PUT_LINE('old p ' || old_pk); /*old is set in oracle 12, but not in oracle18/19*/
    DBMS_OUTPUT.PUT_LINE('old n ' || old_name); /*old is set in oracle 12, but not in oracle18/19*/
    DBMS_OUTPUT.PUT_LINE('new p ' || new_pk); /*new is set correctly*/
    DBMS_OUTPUT.PUT_LINE('new n ' || new_name); /*new is set correctly*/
    DBMS_OUTPUT.PUT_LINE('TEST END');


END;
/

/*test the log*/
update MAT_VIEW_TEST set name = 'Test' where pk = 1;
commit;

有什么想法在 Oracle 中发生了变化，或者我们可以做些什么来获取触发器中的旧值？

标签： databaseoracletriggersmaterialized-views

解决方案

我没有 12c 来重新运行您的测试，但是我在 21c 上做了，并且使用您显示的触发器，旧值永远不会显示，无论是插入（正常）还是更新（这就是您所抱怨的）关于）。当我将触发器更改为“插入或更新或删除时”并重新运行更新时，我可以看到旧值。因此，刷新过程将您的 UPDATE 转换为 DELETE/INSERT，因此在删除旧行时会使用旧值。

推荐阅读