CODEWITHSUNDEEP
sqloracle-databaseview
Tito Rahman
Tito Rahman

Reputation: 107

Create table from table/view?

I have a weird scenario. I tried to see if I could find any help on the topic, but I either don't know how to search for it properly, or there is nothing to find.

So here is the scenario.

I have a table A. From Table T_A, I created a view V_B. Now, I can make UPDATES to V_B, and it works just fine. Then when I create a view V_C which is an UNION of T_A and T_D, the view V_C is un-Updateable. I understand the logic behind why that is the case.

But my question is, is there something I can do where I combine 2 tables and am able to update?

Maybe in a way have table T_D extend T_A?

Some extra information: T_A has items 1-10 and T_D has items 100 - 200. I want to join them so there is a table/view which is updateable that has items 1-10 and 100-200.

Upvotes: 0

Views: 158

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

If you have a non-updatable view, you can always make it updatable by defining instead of triggers on the view. That means that you would need to implement the logic to determine how to translate DML against the view into DML against one or both of the base tables. In your case, it sounds like that would be the logic to figure out which of the two tables to update.

A couple of points, though.

  1. If T_A and T_D have non-overlapping data, it doesn't make sense to use a UNION, which does an implicit DISTINCT. You almost certainly want to use the less expensive UNION ALL.
  2. If you find yourself storing data about items in two separate tables, only to UNION ALL those two tables together in a view, it is highly likely that you have an underlying data model problem. It would seem to make much more sense to have a single table of items possibly with an ITEM_TYPE that is either A or D.
  3. It may be possible to make your view updatable if you use a UNION ALL and have (or add) non-overlapping constraints that would allow you to turn your view into a partition view. That's something that has existed in Oracle for a long time but you won't find a whole lot of documentation about it in recent versions because Oracle partitioning is a much better solution for the vast majority of use cases today. But the old 7.3.4 documentation should still work.

Upvotes: 2

Related Questions