EXAMPLE OF AUTO INCREMENT COLUMN IN ORACLE

AS SALAMO ALAIKUM WA RAHMATULLAH



CREATE TABLE TBL_ITEM
(
ITM_ID NUMBER(5),
ITM_CODE VARCHAR2(30) UNIQUE NOT NULL,
ITM_DESC VARCHAR2(250),
ITM_PRICE NUMBER(10,3),
ITM_QTY NUMBER(3),
ITM_CRT_DT DATE,
ITM_UPD_DT DATE,
ITM_IS_DEL CHAR(1),
PRIMARY KEY(ITM_ID),
CHECK (ITM_IS_DEL IN ('Y','N'))
)


CREATE SEQUENCE SEQ_TBL_ITM_ID START WITH 1 INCREMENT BY 1


CREATE OR REPLACE TRIGGER TRG_ITM_ID
BEFORE INSERT ON TBL_ITEM
FOR EACH ROW
BEGIN
    SELECT SEQ_TBL_ITM_ID.NEXTVAL INTO :NEW.ITM_ID FROM DUAL;
END;
/

INSERT INTO TBL_ITEM (ITM_CODE,ITM_DESC,ITM_PRICE,ITM_QTY,ITM_CRT_DT,ITM_UPD_DT,ITM_IS_DEL)
VALUES('LS-GC17103','GRIFFIN FLAT AUX CABLE CONNECT + PLAY',5.000,10,SYSDATE,SYSDATE,'N')

SELECT * FROM TBL_ITEM


--
MA ASALAAM
PASSION 4 ORACLE