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

Last updated on JUNE 17, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
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.

 

TEST CASE:

create user testctl identified by testctl
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant dba to testctl;

create user mview identified by mview
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant dba to mview;

create user DWCHANGE identified by DWCHANGE
default tablespace users
temporary tablespace temp
quota unlimited on users;

grant dba to DWCHANGE;

connect testctl/testctl

create table testctl.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 MVIEW, DWCHANGE;

drop materialized view mview.test_mv;

drop materialized view log on testctl.test;
drop materialized view log on testctl.test

CREATE MATERIALIZED VIEW LOG ON testctl.test
TABLESPACE users
NOCACHE
LOGGING
NOPARALLEL
WITH PRIMARY KEY, SEQUENCE
(DRDL01,DRDL02,DRSPHD,DRUDCO,DRHRDC,DRUSER,DRPID,DRUPMJ,DRJOBN,DRUPMT)
INCLUDING NEW VALUES;

connect mview/mview

CREATE MATERIALIZED VIEW MVIEW.test_mv
TABLESPACE users
BUILD IMMEDIATE
USING INDEX TABLESPACE mview
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
select DRSY, DRRT, DRKY, current_date AS Refresh_Date
from testctl.test;

connect testctl/testctl

BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname =>'TESTCTL', tabname => 'TEST');
END;
/

connect DWCHANGE/DWCHANGE

CREATE OR REPLACE TRIGGER dwchange.test_trigger
AFTER INSERT
ON TESTCTL.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 testctl/testctl

set serveroutput on;
insert into testctl.test (DRSY,DRRT,DRKY) VALUES ('H00','IS','T');
Executing Trigger....                                                          
Finishing Trigger....                                                          

commit;


INSERT INTO testctl.test
(Select
'H99',
DRRT,
DRKY,
DRDL01,
DRDL02,
DRSPHD,
DRUDCO,
DRHRDC,
'JRH',
DRPID,
DRUPMJ,
DRJOBN,
DRUPMT from testctl.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 testctl.test
(Select
'H99',
DRRT,
DRKY,
DRDL01,
DRDL02,
DRSPHD,
DRUDCO,
DRHRDC,
'JRH',
DRPID,
DRUPMJ,
DRJOBN,
DRUPMT from testctl.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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms