This forum is currently locked. You can't register or post questions at this time. (read more)

Trigger or M-View...

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Art
Member
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Trigger or M-View...

Postby Art » Wed Oct 02, 2013 4:51 pm

Hello,
I'm looking for the better/best solution to compare data in a table before update and after. What is better solution - trigger and temp. table/global temp or materialized view (refreshed nightly) please? The Flashback query has its limitations - not considering this at this point.

The table is not big, less then 1000 rows. It may grow in the future, but not much. The whole idea is to compare today's last value of some indicator in that table with its today's initial value. For ex., init. value = N (morning). Then users update it to Y during the day. Then later it is updated to back to N (end of day). In this case I need to ignore this row. Otherwise, I need to update some other table based on the value of this indicator. The job to compare and update will run at night. Which solution is better please, trigger or m-view? Which is better for big tables, possibly with million(s) rows please?
Thank you Tim and all.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Trigger or M-View...

Postby Tim... » Thu Oct 03, 2013 4:46 pm

Hi.

Personally I would go with an MView, or manually use a separate table. I would not do anything with triggers if I could help it as it will add an overhead on your system that you don't need.

You say you are not considering flashback query. Is that just because of uncertainty over undo?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Art
Member
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Re: Trigger or M-View...

Postby Art » Thu Oct 03, 2013 5:46 pm

Thank you, Tim.
Yes, the undo uncertainty and trying to keep it simple and reliable... For some reason, my superiors do not like the m-view solution. They like trigger and temp table.
The before update trigger already exists. All I have to do is to add insert statement to it to create a row in some temp table whenever user updates that indicator...
Thank you very much.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Trigger or M-View...

Postby Tim... » Thu Oct 03, 2013 5:56 pm

Hi.

OK. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests