In our tool we use triggers on materialized views in order to create log-entries (and do some other things) when a transaction is commited. The code works good in Oracle 12. In Oracle 19 the old values in that trigger (":old") seems to be lost.
Investigations: This seems to be the case in the combination of materialized views/triggers. If we set the same trigger on a table the logs are generated correctly (but we do not get the transaction-awareness which is required).
I have created a MWE and added comments to the DBMS_OUTPUT-Lines which describe what we see in oracle 12 and 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;
Any ideas what was changed in Oracle or what we could do to get the old values in our trigger?
I don't have a 12c to rerun your tests, but I did on a 21c, and with the trigger you show, the old values are never shown, neither on insert (normal) nor on update( which is what you're complaining about). When I changed the trigger to be 'on insert or update or delete', and reran an update, I can see the old values. So, the refresh process is converting your UPDATE to DELETE/INSERT, hence the old values when it is deleting the old row.