OraFAQ Forum: SQL & PL/SQL » Trigger on Materialized View Log tables

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on Materialized View Log tables (ORACLE 11G Version 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger on Materialized View Log tables [message #658211]

Wed, 07 December 2016 03:24

arunrajv@yahoo.com
Messages: 8
Registered: December 2016

Junior Member

Hi Guys,
Please help with the below query.
Requirement: To track DML changes on multiple tables and to populate its consolidated records in one master table by having triggers on the MLOG tables.
Action : I have created triggers on MLOG tables, few of them works and few are failed.

Query: Need suggestion, Is it advisable to have triggers on MLOG$_TABLES (Materialized view log tables ), If yes , Is there any restriction to have so.

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658217 is a reply to message #658211]

Wed, 07 December 2016 04:24

John Watson
Messages: 9002
Registered: January 2010
Location: Global Village

Senior Member

I think that you misunderstand the purpose of materialized view logs. They are to maintain materialized views. No other purpose. You probably need to look at configuring Change Data Capture.

By the way, I wish you wouldn't say "records"" when you mean "rows".

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658218 is a reply to message #658217]

Wed, 07 December 2016 04:41

arunrajv@yahoo.com
Messages: 8
Registered: December 2016

Junior Member

Thanks for the suggestion and correction John.
I would definitely go with CDC.

Still as doubt, is it possible to have working trigger on MLOG tables .

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658225 is a reply to message #658218]

Wed, 07 December 2016 06:29

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just try it.

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658231 is a reply to message #658225]

Wed, 07 December 2016 06:43

arunrajv@yahoo.com
Messages: 8
Registered: December 2016

Junior Member

Tried. Triggers working for some log table and not working for some log table. Is there any restrictions.

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658236 is a reply to message #658231]

Wed, 07 December 2016 06:50

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Give case it works and case it does not and of course, give your triggers.
In short, as express in the OraFAQ Forum Guide, Use SQL*Plus and copy and paste your session, the WHOLE session including all objects creation statements.
Before, Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658239 is a reply to message #658211]

Wed, 07 December 2016 07:30

_jum
Messages: 577
Registered: February 2008

Senior Member

The question is already answered at: OTN Discussion Forum

Report message to a moderator

Re: Trigger on Materialized View Log tables [message #658275 is a reply to message #658239]

Wed, 07 December 2016 23:02

arunrajv@yahoo.com
Messages: 8
Registered: December 2016

Junior Member

Thanks John , Michael for all your time. The approach I considered to track the changes in rows is not correct. I have been advised to go with CDC in other forum as in the previous reply from _jum.

Report message to a moderator

Previous Topic:	Outer Joins and To_Char
Next Topic:	ORA-30006 Resource Busy, acquired with Wait timeout expired

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 13 23:29:59 CDT 2026