Auto increment Sequence using Trigger

CREATE TABLE test (

  ID           NUMBER    NOT NULL,

  DESCRIPTION  VARCHAR2(30)  NOT NULL);

 

ALTER TABLE test ADD (

  CONSTRAINT test_pk PRIMARY KEY (ID));

 

CREATE SEQUENCE test_seq;

 

CREATE OR REPLACE TRIGGER test_insert

BEFORE INSERT ON test

FOR EACH ROW

 

BEGIN

  SELECT test_seq.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

/

 

SQL> insert into test(description) values('This is a test');

1 row created.

 

SQL> insert into test(description) values('Second rows for test');

1 row created.


SQL> commit;

Commit complete.

 

 

SQL> insert into test(description) values('Third rows for test');

1 row created.

 

 

SQL> rollback;

Rollback complete.

 

SQL>  insert into test(description) values('Fourth rows for test');

1 row created.

 

 

SQL> select * from test;

 

        ID DESCRIPTION

-----------------------------------------------------------------------

         1 This is a test

         2 Second rows for test

         4 Fourth rows for test

Comments