Scribd
Upload
72 views

Create Table Test (Id Number, Testdata Varchar2 (255) )

This document provides instructions for creating a sequence and trigger in Oracle to automatically generate unique IDs for table entries. It explains how to: 1. Create a sequence to generate unique numbers. 2. Create a table with an ID column. 3. Create a trigger to populate the ID column with the next number from the sequence for each new row inserted.

Uploaded by

utpalbasak
Copyright
© Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
72 views

Create Table Test (Id Number, Testdata Varchar2 (255) )

This document provides instructions for creating a sequence and trigger in Oracle to automatically generate unique IDs for table entries. It explains how to: 1. Create a sequence to generate unique numbers. 2. Create a table with an ID column. 3. Create a trigger to populate the ID column with the next number from the sequence for each new row inserted.

Uploaded by

utpalbasak
Copyright
© Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 2

Suppose you have a database and you want each entry to be identified by a unique number.

You
can do this easily in mysql by specifying "auto_increment" for your number, but Oracle makes
you work a little more to get it done.

Here is one way to do it by creating two database objects, a sequence and a trigger. I find myself
wanting to do this every now and then but not often enough that I remember the syntax from
time to time, so I decided it was time to write myself up a little cheat sheet. This is an extremely
basic outline, so please try it first on a test table if you don't know what you're doing.

1. Let's say we have a table called "test" with two columns, id and testdata. (This is just a dumb
quick example, so I won't bother to specify any constraints on id.)

create table test (id number, testdata varchar2(255));

2. Next we'll create a sequence to use for the id numbers in our test table.

create sequence test_seq


start with 1
increment by 1
nomaxvalue;

You could change "start with 1" to any number you want to begin with (e.g. if you already have
213 entries in a table and you want to begin using this for your 214th entry, replace with "start
with 214"). The "increment by 1" clause is the default, so you could omit it. You could also
replace it with "increment by n" if you want it to skip n-1 numbers between id numbers. The
"nomaxvalue" tells it to keep incrementing forever as opposed to resetting at some point. i (I'm
sure Oracle has some limitation on how big it can get, but I don't know what that limit is).

3. Now we're ready to create the trigger that will automatically insert the next number from the
sequence into the id column.

create trigger test_trigger


before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

Obviously you would replace "test_trigger" with something a little more meaningful for the
database table you want to use it with, "test" would be your table name, and the "id" in :new.id
would be replaced with the name of the column. Every time a new row is inserted into test, the
trigger will get the next number in the sequence from test_seq and set the "id" column for that
row to whatever the sequence number is. Note that sequences sometimes appear to skip numbers
because Oracle caches them to be sure that they are always unique, so this may not be your ideal
solution if it's really important that the id is exactly sequential and not just mostly sequential and
always unique.
Greg Malewski writes:

You've demonstrated an implementation using triggers. This is not necessary, since instead it can
be included as part of the INSERT statement. Using your example, my INSERT statement would
be:

insert into test values(test_seq.nextval, 'voila!');

Here are a couple of questions the above might raise. This is pretty intuitive stuff, but I'm aiming
it at the Oracle newbie since no expert would be reading this page anyway.

How do you tell what sequences and triggers are already out there?

select sequence_name from user_sequences;


select trigger_name from user_triggers;

How do you get rid of a sequence or trigger you created?

drop sequence test_seq;


drop trigger test_trigger;

Again, replace test_seq and test_trigger with the specific names you used. You can also keep the
trigger but disable it so it won't automatically populate the id column with every insert (and
enable it again later if you want):

alter trigger test_trigger disable;


alter trigger test_trigger enable;

You might also like