Auto Increament di Oracle

4 Apr

Auto Increament di Oracle, membutuhkan 2 komponen yaitu
1. sequence
2. triger

demonya :
1. seandainya sudah ada tabel tr_penjualan
SQL> desc tr_penjualan;
Name                                      Null?    Type
—————————————– ——– —————————-
TGL_TRANSAKSI                                      DATE
KODE_CABANG                                        VARCHAR2(10)
KODE_KASIR                                         VARCHAR2(10)
KODE_ITEM                                          VARCHAR2(7)
KODE_PRODUK                                        VARCHAR2(12)
JUMLAH_PEMBELIAN                                   NUMBER(38)

SQL> SELECT COUNT(*) FROM tr_penjualan;
COUNT(*)
———-
437724

2. lakukan penambahan kolom untuk Auto Increament, misal no_id
SQL> ALTER TABLE tr_penjualan ADD no_id NUMBER;

3. buat sequence;
SQL> CREATE SEQUENCE  seq_tr_penjualan
MINVALUE 1
MAXVALUE 99999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

4. buat trigger
SQL> CREATE OR REPLACE TRIGGER TR_NAME
BEFORE INSERT
ON tr_penjualan
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT seq_tr_penjualan.nextval INTO :NEW.no_id FROM dual;
END;

5. lakukan update agar no_id memiliki no urut
SQL> update TR_PENJUALAN set NO_ID=seq_tr_penjualan.nextval;
SQL> COMMIT;

6. tambahkan agar kolom no_id NOT NULL
ALTER TABLE tr_penjualan MODIFY no_id NOT NULL;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: