Re: Creating primary key values with trigger

ppf_at_fct.unl.pt (Pedro Pimentel) writes:

> I think it would be nice if oracle could create primary key values
> automagically (like 'counter' fields in MS Access). I was thinking about
> something like this:
 

> create table myTable (
> ID number(5) CONSTRAINT pk_myTable PRIMARY KEY,
> field1 char(30),
> ....
> ) ;
 

> create sequence seq_myTable ;
 

> create or replace trigger check_myTable
> before insert
> on myTable
> for each row
> begin
> :new.ID := seq_myTable.nextval ;
> end ;
 

> This doesn't work because Oracle checks for null values in the
> ID field before running the trigger.
 

> This way I have to use a default value (usually 0) in the ID field in
> the form application (I'm using MS Access, much better than SQL*Forms :) )
> so that oracle passes by the not null check and then runs the trigger.
 

> Any other ideia, comments ?

I'm not sure about this, but...
The SQL manual seems to indicate that a Before Insert (but not For Each Row) Trigger will fire before the insert statement, as a whole, is executed, implying that such a trigger will fire Before Oracle gets round to checking those Not Null constraints. So, if you create a Before Insert (but not For Each Row) trigger that essentially disables that pk_myTable constraint (and any associated Not Null constraint), and create an After Insert (but not For Each Row) trigger that re-enables those constraints, then this Might work. I haven't tested this, though; it's just an idea.

Hope it works; could you let us all (me, at least) know if it does work.

> --
> Pedro Pimentel
> e-mail: ppf_at_fct.unl.pt
> www: http://ester.fct.unl.pt/pedro1.htm

Hank Robinson
Oracle DBA
University of Warwick Received on Mon Sep 12 1994 - 12:55:36 CEST