Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint

Re: table constraint

From: mlynch147 <martin.lynch_at_raytheon.co.uk>
Date: Tue, 26 Jun 2007 03:52:07 -0700
Message-ID: <1182855127.118263.72890@g4g2000hsf.googlegroups.com>


On Jun 25, 12:04 pm, rwij..._at_gmail.com wrote:
> > Will your solution do this?
>
> No sorry, I misinterpreted the question. The solution I presented
> makes sure that in each 3 minute interval there is only 1 record,
> which is different from your requirement that each entry must have a
> gap of at least 3 minutes (or is it 3 seconds?) from the nearest other
> rows.
>
> I was thinking about solving this with a fast refreshable materialized
> view, but it has too many restrictions (and it would only enforce the
> constraint at commit time)
>
> Two options left:
>
> 1) use an api for the insert. This involves revoking all insert/update
> and delete privileges from the table and grant access to an api to the
> users. This api will do the check in PL/SQL during insert and update.
>
> 2) use database triggers. You would have to circumvent the mutating
> table problem by creating a before statement, an after row insert/
> update and an after statement insert/update trigger. I would not
> recommend this path because it has several disadvantages:
> - you would have to take into account that you are dealing with a
> multi user environment, so you would have to implement some locking
> - it is complex
> - it slows down future DML actions considerably.
>
> Regards,
> Rob.
>
> By the way: does anybody know how I can make my name appear instead of
> my email address?

Hi Rob

Two things here...
1. i got the times wrong.... it is a 3 minute window (though my examples showed the seconds being updated) 2. i will be doing this though a java app which is possibly on another box and this table will be getting hit 50-100 times a second so i was just thinking of performance....

Without the sort of constraint im talking about, i would have to create a query and if the count returned zero, then i know its ok to do the insert (2 hits - bad).

maybe a stored procedure would be the way to go as i would only have to make one call and then the stored proc would decide whether to make the insert or not.... do you think this is a good idea

thanks for your input to date
Marty Received on Tue Jun 26 2007 - 05:52:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US