Re: AW: Constraint violation in spite of trigger with sequence
Date: Mon, 8 Feb 2010 13:36:30 +1000
Message-ID: <4B6F86BE.8000705_at_technologyonecorp.com>
It does not have to be an official application that makes the update. If it happens monthly or so, but with some regularity, there may be someone using e.g. a home-brewn Access app or Excel spreadsheet to create monthly//quarterly/yearly reports and accidentally update some num value. It is very easy in either of these tools to update values without even realising it...
Do your users have a database username & password, or is the database only accessible via sanctioned middleware applications?
Cheers,
Tony
Around 5/02/2010 10:46 PM, Nigel Thomas said:
> Sorry, you both spotted my silly error on which sequence was being
> selected conditionally. Oops.
>
> The main point is that any UPDATE of NUM can cause an ORA-0001. If
> there is an edge condition in the client app that results in NUM being
> updated occasionally, that would account for the intermittent
> behaviour. Oracle Forms (for example) can easily be configured to
> generate an update to PK/UK columns (because it generates a generic
> update, and updates by rowid). I wouldn't put it past an O/R mapping
> like Hibernate to be similarly prone to mis-configuration. If the
> application sometimes mistakenly writes to that field, your error will
> appear.
>
> The first thing to do is to verify (directly from V$SQL and if
> possible from the source code) whether there is _any_ SQL which could
> possibly set AUFT.NUM, even if it is not designed to. Don't accept the
> word of any developer! He's not lying, but he can be mistaken.
>
> HTH
>
> Nigel
>
>
>
> On 5 February 2010 12:32, Mark W. Farnham <mwf_at_rsiz.com
> <mailto:mwf_at_rsiz.com>> wrote:
>
> I thought the constraint violation was on NUM, not ID. If the
> constraint violation is on ID, then the idea of garbage in :new_id
> is valid.
>
>
>
> Updates to NUM, inadvertant or otherwise, seem the likely cause
> since the behavior is intermittent. The other thing that could be
> intermittent that comes to mind would some user’s schema scoping
> referencing a different definition of the sequence.
>
>
>
> <start whining about decades old enhancement requests>
>
> Too bad ORA-00001 does not routinely spit out the duplicate value,
> along with the two PKs in the case the column set in question is
> not the PK, in which case the rowid of the existing record could
> be useful.
>
> <end whining about decades old enhancement requests>
>
>
>
> And while sequences can skip numbers for a variety of
> circumstances and conditions mostly revolving around sequence
> cache and crashes, I cannot remember ever seeing a single sequence
> ever delivering duplicates without wrapping. (And I hope you’re
> not wrapping on a sequence intended to deliver monotonically
> increasing unique values.)
>
>
>
> mwf
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 07 2010 - 21:36:30 CST