Re: Complex Primary Key Validation

From: Keith Boulton <kboulton_at_ntlworld.com>
Date: Mon, 21 Jan 2002 20:20:47 -0000
Message-ID: <uG_28.56558$Hg7.6317174@news11-gui.server.ntli.net>

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.
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

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.

create table FredParent( Id number not null ); insert into FredParent values(1);

create table fred
( id number not null
, fromdate date not null
, todate date not null
);

alter table fred

   add constraint fred_pk
   primary key ( id, fromdate, todate );

• try with a trigger
• use a package to get round mutating table problem create or replace package freddata as
  • old fashioned table type, can't be bothered to look up the new type tFredId is table of Fred.Id%type index by binary_integer; Inserted tFredId; end; /
• statement level trigger to clear out table create or replace trigger FredBI before insert on Fred begin FredData.Inserted.Delete; end; /
• row level trigger to save the inserted rows create or replace trigger FredAIR after insert on Fred for each row begin FredData.Inserted(FredData.Inserted.Count+1) := :new.Id; end; /
• statement level trigger to process table create or replace trigger FredAI after insert on Fred declare cursor c(pId in Fred.Id%type ) is select FromDate, ToDate from Fred where Id = pId order by FromDate; maxdate date; dummy varchar2(1); begin for i in 1..FredData.Inserted.Count loop maxdate := null;
  • we have to serialise access to this id select 'x' into dummy from FredParent where Id = FredData.Inserted(i) for update; for r in c(FredData.Inserted(i)) loop if maxdate is not null then if maxdate >= r.fromdate then raise_application_error( -20000, 'Overlapping record for id '|| FredData.Inserted(i)); end if; end if; maxdate := r.todate; end loop; end loop; FredData.Inserted.Delete; end; /

Received on Mon Jan 21 2002 - 14:20:47 CST