Do I have to use SEQUENCE?

As we all know that if we want a not null PK to be populated automatically while inserting data we need to create a SEQUENCE for it.

BUT my question is , is there a way that we can just attached a constriant or something to the table that will generate these unique numbers automatically instead of us creating sequences manually???

Ans1:
Yes, you might want to create a BEFORE INSERT ROW trigger on the table. In this trigger you can call a stored procedure or you can code your PL/SQL in the triggerbody itself.

The code can look like:
{in the stored procedure/package}

Procedure BIR
( p_pk in out number
)
is
begin
if p_pk is NULL
then
        select sequence_name. nextval
        into p_pk
        from sys.dual;
end if;
end BIR;

and you call it in the trigger like

BIR(:new.pk)

--
The use of max(pk)+1 WILL decrease your performance during inserts.

Ans2:
I think another option is to create a DB trigger and select the max(PK) + 1 into the new record. However, this way may slow down when data grows.

Hosted by www.Geocities.ws