How to create auto sequential numbered PK? 1

[macknox]

Complete rookie...

My book assumes that I have Designer 6i and tells me to do something in the gui that will auto sequence those numbers.

Excitingly enough, the code provided with the book is all in a .dmp file which I can't read with Designer 6i.

I also have to automatically do 'created by' and 'updated by' columns, but I have some trigger code for that from another book.

I would imagine that I could trigger this by calling the MAX of the column in the table I am about to change and adding 1. As long as the trigger doesn't directly change the table, only changing the :new row values I don't run into a 'mutating' table problem, right?

Of course, I only barely know what the code for that solution would look like.

Thanks for your time,

Kevin

 

[carp]

A better (faster, "cleaner&quot approach would be to use a sequence. In a SQL*Plus session, issue the following command:

CREATE SEQUENCE sequence_name_of_your_choice NOCYCLE;

(I'm sure there is a way to do this in Designer, but since I'm not very familiar with the tool, I'll leave it for others to tell us the easy way!)

Now whenever you insert a row into the table, populate the primary key column with the value sequence_name_of_your_choice.nextval.

 

[stevecal]

Furthermore, you could have a reference table which associates all of your tables with a list of the sequences you have created for them. Then you add a function with a lookup into this table to generate the next sequence number for you. We do this very happily so that all I have to do is run my packaged function passing it the table name and it will get me my next sequence value...

 

[MikeLacey]

Stevecal's solution sounds a bit complex to be honest, but we use something like carp's idea.

We create an "on insert" trigger, test the value of the PK and assign a value from the sequence if it is NULL.

From one of our databases:
[tt]
CREATE OR REPLACE TRIGGER "CSPR"."CSHDPRSP_BIU" BEFORE INSERT OR UPDATE ON "CSPR"."CSHDPRSP" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
Begin
/*
* 002 Set GTREP='000' if null on insert/update.
*/
If ( :new.GTREP Is Null ) Then
:new.GTREP := '000';
End If;
/*
* 000 17.09.1997 Mike-L Original version, set hdprsp if null.
*/
If ( :new.hdprsp Is Null ) Then
Select HDPRSP_SEQ.NextVal Into :new.hdprsp From DUAL;
End If;
/*
* 001 15.10.1997 Mike-L Set HDORNM initcap and HDSORT Upper.
*/
:new.HDORNM := Initcapnew.HDORNM);
:new.HDSORT := Uppernew.HDSORT);
End;

;
[/tt]
You'll notice that it's "on insert or update", that's because we do other things to the data as well.

HDPRSP is the PK for this table. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.