0

I have a view which dynamically returns the values from different tables. I needed to insert or delete into some table from that view, whenever the view adds or decreases the data. Should i be using stored procedure or something like that

shishir ghimire
  • 21
  • 4
  • This question has absolutely nothing to do with Delphi, so I'm removing the tags. This is strictly an Oracle SQL question. – Ken White Jan 28 '16 at 13:31
  • What do you mean with "whenever view adds or decreases data"? Could you provide some use case, how do you use it? – Petr Pribyl Jan 28 '16 at 13:51
  • Looks like you need a [Materialized View](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm). – Sameer Mirji Jan 28 '16 at 14:47
  • A view is just a canned query on tables, it does not physically store data. You either need to use a materialized view, or use a solution of triggers on the underlying tables that the view queries to identify when the contents of the view change. – Michael Broughton Jan 28 '16 at 15:43
  • this might help http://stackoverflow.com/questions/1652995/in-oracle-is-it-possible-to-insert-or-update-a-record-through-a-view – Sandeep Jan 28 '16 at 16:40
  • Yes Materialized view can solve this. Thanks to all – shishir ghimire Feb 01 '16 at 09:32

1 Answers1

0

As @MichaelBroughton already stated, a view does not physically store data and a view has no "knowledge" about when its base data changes.

I suggest you think about creating triggers on the base tables and let these triggers handle updates of the target table. If applicable in your context, you can of course have several triggers all executing the same stored procedure where the data propagation is done.

A matrialized view can be a solution, too, since you can simply create a trigger on that view, but at the cost of actually storing all the data your view provides.

Michael Schaefers
  • 707
  • 4
  • 17