|
|
| Re: how to create table with automatic sequence number [message #23143 is a reply to message #23142] |
Tue, 19 November 2002 20:53  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In Oracle, this is done by creating a table with a column of number data type as the primary key, creating a sequence, and creating a before insert row trigger that automatically populates the number column of the table, using the next value of the sequence, any time an insert is done on the table. So, when you insert, you only have to insert values into the other columns and the number column that is the primary key is automatically populated. The minimal syntax for accomplishing this is listed below, along with a brief demonstration of inserting three rows, then selecting from the table to view the sequential values that were automatically populated.
SQL> -- create table
SQL> -- with column of number data type as primary key:
SQL> CREATE TABLE tab_name
2 (seq_col NUMBER PRIMARY KEY,
3 other_column VARCHAR2 (10))
4 /
Table created.
SQL>
SQL>
SQL> -- create sequence:
SQL> CREATE SEQUENCE tab_name_seq_col
2 /
Sequence created.
SQL>
SQL>
SQL> -- create before insert row trigger on table
SQL> -- that automatically populates primary key column
SQL> -- using next value of sequence:
SQL> CREATE OR REPLACE TRIGGER tab_name_bit
2 BEFORE INSERT ON tab_name
3 FOR EACH ROW
4 BEGIN
5 SELECT tab_name_seq_col.NEXTVAL
6 INTO :NEW.seq_col
7 FROM DUAL;
8 END tab_name_bit;
9 /
Trigger created.
SQL>
SQL>
SQL> -- test:
SQL> INSERT INTO tab_name (other_column) VALUES ('testa')
2 /
1 row created.
SQL> INSERT INTO tab_name (other_column) VALUES ('testb')
2 /
1 row created.
SQL> INSERT INTO tab_name (other_column) VALUES ('testc')
2 /
1 row created.
SQL> SELECT * FROM tab_name
2 /
SEQ_COL OTHER_COLU
---------- ----------
1 testa
2 testb
3 testc
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
