Re: Limit UPDATE statements to affect only one row

bernard (bernard_at_bosvark.com) wrote:
> Can not misuse instead of trigger on table
> ORA:25002: cannot create INSTEAD OF triggers on tables
> Version:10.2.0.1
>
> So a view it will be (nope is can't be used I'm told) since it breaks
> Hibernate (aaaaargh!!!!!!) which discovers the data relationships
> through foreign keys on tables to effectively generate the code
> (ouch!).
>
> Any more ideas are welcome.
>
> Regards
> Bernard
>
> P.S. Fight the use Hibernate in all circumstances unless you want to
> limit the functionality of Oracle by 80% since Hibernate attempts to
> create database independence (yea right).

I do not think that I have ever used the feature, but I believe that you can declare constraints on views. Oracle does not enforce the constraints but the definitions would be there. Perhaps that would allow Hibernate to work.

>From 9.2 Concepts manual
>>

 In some environments, database administrators, for schema complexity or security reasons, define views on fact and dimension tables. Oracle provides the ability to constrain views. By allowing constraint definitions between views, database administrators can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in a restricted environment.

Only logical constraints, that is, constraints that are declarative and not enforced by Oracle, can be defined on views. The purpose of these constraints is not to enforce any business rules but to identify multidimensional data. The following constraints can be defined on views:

Primary key constraint
Unique constraint
Referential Integrity constraint
<<

So front every table with a view that has instead of trigger(s) defined on it to point to the base table and define PK, UK, and FK that minic the base table relationships.

HTH -- Mark D Powell -- Received on Fri Aug 11 2006 - 08:18:28 CDT