Re: How to LOCK TABLE/READ ONLY table in own schema after data insertion

On 7 Jun 2006 01:58:22 -0700, sanjeev_atvankar_at_hotmail.com wrote:

>>>You would need to two things
>>>1 set up a table with 'closed out' periods
>>>2 create a view on top on the hist table, which will join the hist
>>>table and the table you just created, allowing to view only data for
>>>'open' periods. The view has to be created 'With check option'
>>>3 The inserting program (or no one) doesn't get insert privilege on
>>>the table, it only gets insert privilege on the view
>>>4 As soon as the insert program gets out of boundaries, the insert
>>>will fail.
>
>I have read your solution But sorry to say that it is confusing for me.
>I am confuse with
> >set up a table with 'closed out' periods (Please clear this point
>more clearly with example if possible or link)
>
>WOULD YOU PLEASE MAKE IT MORE ELABORATIVE ?
>
>Its urgent.

It's not urgent. This is a volunteer newsgroup. For urgent matters there is paid support.

I'm not sure what is too difficult about a table with closed out periods.
After a certain date it is no longer allowed to mutate pieces of history. You need to keep track of them, anything beyond that date can be updated.
In order to make sure corrections are possibly it is better to divide the 'non-updatable data' in periods of say 4 weeks So you simply get an extra where clause

and not exists
(select 1
 from <closed out table>
 where <hist date from main query> between begindate and enddate )

If the date is in the closed out period, the subquery is going to return 1, and the main query will return nothing. If you append this to your view, and create your view with check option, this view won't allow to insert/update any records which are in closed out periods.

Hth

--
Sybrand Bakker, Senior Oracle DBA