My Oracle Support Banner

Trigger On Materialized View Log Not Firing On Insert After Upgrading to 11G Release 2 (Doc ID 1560808.1)

Last updated on NOVEMBER 14, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

 

An after insert trigger has been placed on a MV log to track changes. This worked fine in 11g Release 1. In 11g Release 2, it  will only now only fire when a regular insert is run on the table that the MV log is built on. If you do a delete or update or a insert into select on that table the trigger will not fire even though rows are still being inserted on the MV log. There is no error.

 NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

 

TEST CASE:

create user <user1> identified by <password>;

grant dba to <user1>;

create user <user2> identified by <password>

grant dba to <user2>;

create user <user3> identified by <password>

grant dba to <user3>;

connect <user1>/<password>

create table <user1>.test ( "DRSY" NCHAR(4), "DRRT" NCHAR(2), "DRKY" NCHAR(10), "DRDL01" NCHAR(30), "DRDL02" NCHAR(30), "DRSPHD" NCHAR(10), "DRUDCO" NCHAR(1), "DRHRDC" NCHAR(1), "DRUSER" NCHAR(10), "DRPID" NCHAR(10), "DRUPMJ" NUMBER(6), "DRJOBN" NCHAR(10), "DRUPMT" NUMBER, CONSTRAINT "TEST_PK" PRIMARY KEY ("DRKY", "DRRT", "DRSY") VALIDATE ) TABLESPACE "USERS";

GRANT SELECT ANY TABLE TO <user2>, <user3>;

drop materialized view <user2>.test_mv;

drop materialized view log on <user1>.test;

drop materialized view log on <user1>.test

CREATE MATERIALIZED VIEW LOG ON <user1>.test

NOCACHE

LOGGING

NOPARALLEL

WITH PRIMARY KEY, SEQUENCE

(DRDL01,DRDL02,DRSPHD,DRUDCO,DRHRDC,DRUSER,DRPID,DRUPMJ,DRJOBN,DRUPMT)

INCLUDING NEW VALUES;

connect <user2>/<password>

CREATE MATERIALIZED VIEW <user2>.test_mv

BUILD IMMEDIATE

USING INDEX

REFRESH FORCE ON DEMAND

WITH PRIMARY KEY

AS

select DRSY, DRRT, DRKY, current_date AS Refresh_Date

from <user1>.test;

connect <user1>/<password>

BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname =>'<user1>', tabname => 'TEST');

END;

/

connect <user3>/<password>

CREATE OR REPLACE TRIGGER <user3>.test_trigger

AFTER INSERT

ON <user1>.mlog$_test

FOR EACH ROW

BEGIN

 

DBMS_OUTPUT.PUT_LINE('Executing Trigger....');

DBMS_OUTPUT.PUT_LINE('Finishing Trigger....');

EXCEPTION

WHEN OTHERS THEN

-- Consider logging the error and then re-raise

RAISE;

END;

/

connect <user1>/<password>

set serveroutput on;

insert into <user1>.test (DRSY,DRRT,DRKY) VALUES ('H00','IS','T');

Executing Trigger....

Finishing Trigger....

commit;

 

INSERT INTO <user1>.test

(Select

'H99',

DRRT,

DRKY,

DRDL01,

DRDL02,

DRSPHD,

DRUDCO,

DRHRDC,

'JRH',

DRPID,

DRUPMJ,

DRJOBN,

DRUPMT from <user1>.test WHERE TRIM(DRSY) = 'H00' AND TRIM(DRRT) = 'IS' and TRIM(DRKY) = 'T');

commit;

1 row created.

 

 

 In 11.1.0.7

SQL> INSERT INTO <user1>.test
(Select
'H99',
DRRT,
DRKY,
DRDL01,
DRDL02,
DRSPHD,
DRUDCO,
DRHRDC,
'JRH',
DRPID,
DRUPMJ,
DRJOBN,
DRUPMT from <user1>.test WHERE TRIM(DRSY) = 'H00' AND TRIM(DRRT) = 'IS' and TRIM(DRKY) = 'T'); 
Executing Trigger....
Finishing Trigger....

Changes

 Upgraded database from 11.1.0.7 to 11.2.0.3

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.