Most of the times we need to increase our database table column automatically increasing on data insertion. As primary key column is unique and not null it is very useful to set the primary key column as automatic.
Create a table named My_table
CREATE TABLE my_table
(tid NUMBER(5,0) NOT NULL,
tname VARCHAR2(20),
CONSTRAINT pk_tid PRIMARY KEY (tid))
/
Create a sequence named seq_my_tab
CREATE SEQUENCE seq_my_tab
start with 1
increment by 1
/
Now you can create a trigger to set the sequence to the table column get increment on data insertion.
CREATE OR REPLACE TRIGGER trg_my_tab
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
SELECT seq_my_tab.NEXTVAL INTO :new.tid FROM dual;
END;
/
Let’s insert some records in to the table.
INSERT INTO my_table (tid, tname) VALUES (100,'Sachin');
INSERT INTO my_table (tname) VALUES ('Vidusha');
INSERT INTO my_table (tname) VALUES ('Malinga');
SELECT * FROM my_table;
| TID | TNAME | |
| 1 | Sachin | |
| 2 | Vidusha | |
| 3 | Malinga | |
| | |
In the output first inserted tid overwrite by the trigger generated value. We can modify our trigger to insert user required values. Here I am going to customize my trigger bit more.
CREATE OR REPLACE TRIGGER trg_my_tab
BEFORE INSERT ON my_table
FOR EACH ROW
DECLARE
max_val NUMBER;
cur_val NUMBER;
BEGIN
IF :new.tid IS NULL THEN
SELECT seq_my_tab.NEXTVAL INTO :new.tid FROM dual;
ELSE
SELECT greatest(nvl(max(tid),0),:new.tid) INTO max_val FROM my_table;
SELECT seq_my_tab.NEXTVAL INTO cur_val FROM dual;
WHILE cur_val < max_val
LOOP
SELECT seq_my_tab.NEXTVAL into cur_val from dual;
END LOOP;
END IF;
END;
/
Let’s insert some records in to the table.
INSERT INTO my_table (tid, tname) VALUES (200,'Chani');
INSERT INTO my_table (tid,tname) VALUES (400,'Minchi');
INSERT INTO my_table (tname) VALUES ('Kumani');
SELECT * FROM my_table;
| TID | TNAME |
| 200 | Chani |
| 400 | Minchi |
| 401 | Kumani |
It can be inserting any number for the primary key column and next automatic number will 1+ max number.
Posts
