Re: Complex Primary Key Validation

On Mon, 21 Jan 2002 20:20:47 -0000, "Keith Boulton" <kboulton_at_ntlworld.com> wrote:

>
>Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in
>message news:3c4c68d8.1165610016_at_news.alt.net...
>> >
>> You could easily use a TRIGGER to fire ON INSERT OR UPDATE to throw
>> an EXCEPTION when the data fails your business rule.
>>
>Not so easy.
>
>First, you have to get round the mutating table problem i.e. you cannot
>access data in the table for which the trigger is fired. This often means
>using a package and pl/sql table which has implications for scalability and
>performance.

Couldn't an AUTONOMOUS_TRANSACTION help?

>Second, you have to prevent the insertion of rows that would violate the
>constraint in another session which normally requires you to lock a parent
>row or something similar, which serialises inserts against a particular id
>again giving the potential for performance/locking issues.
>Without this, the following scenario can occur:
>
>Session A Session B
>insert row
>check data ok
> insert row that violates constraint in
>conjunction with A
> check data cannot see uncommited row
> returns ok
>commit
> commit
>
>The result is corrupt data
>

This is a good point. So, it isn't so easy after all.

>I have attached outline code to do the job, though it needs tidying up with
>comments, error handling etc. It also assumes that the number of rows per id
>is small, as it iterates through them.
>
>An alternative, if the dates are precise only to the day, is to have a table
>of Id, EffectiveDate populated by trigger expanding all intermediate days.
>This has a primary key on Id, EffectiveDate and prevents overlapping ranges
>being entered.

I was wondering about that. except I didn;t think of making it precise to the day. Precise to the second could use quite a few rows.

Brian Received on Tue Jan 22 2002 - 08:50:31 CST