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: <rwijk72_at_gmail.com>
Date: Mon, 25 Jun 2007 16:04:42 -0000
Message-ID: <1182787482.857088.107840@w5g2000hsg.googlegroups.com>


> 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? Received on Mon Jun 25 2007 - 11:04:42 CDT

Original text of this message

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