Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on Materialized View Log tables (ORACLE 11G Version 11.2.0.3.0)
icon3.gif  Trigger on Materialized View Log tables [message #658211] Wed, 07 December 2016 03:24 Go to next message
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.
Re: Trigger on Materialized View Log tables [message #658217 is a reply to message #658211] Wed, 07 December 2016 04:24 Go to previous messageGo to next message
John Watson
Messages: 7663
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".
Re: Trigger on Materialized View Log tables [message #658218 is a reply to message #658217] Wed, 07 December 2016 04:41 Go to previous messageGo to next message
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 .
Re: Trigger on Materialized View Log tables [message #658225 is a reply to message #658218] Wed, 07 December 2016 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just try it.

Re: Trigger on Materialized View Log tables [message #658231 is a reply to message #658225] Wed, 07 December 2016 06:43 Go to previous messageGo to next message
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.
Re: Trigger on Materialized View Log tables [message #658236 is a reply to message #658231] Wed, 07 December 2016 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Trigger on Materialized View Log tables [message #658239 is a reply to message #658211] Wed, 07 December 2016 07:30 Go to previous messageGo to next message
_jum
Messages: 533
Registered: February 2008
Senior Member
The question is already answered at: OTN Discussion Forum
Re: Trigger on Materialized View Log tables [message #658275 is a reply to message #658239] Wed, 07 December 2016 23:02 Go to previous message
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.
Previous Topic: Outer Joins and To_Char
Next Topic: ORA-30006 Resource Busy, acquired with Wait timeout expired
Goto Forum:
  


Current Time: Wed Nov 14 07:23:12 CST 2018