Penomoran Faktur Otomatis Menggunakan Trigger Before Insert di MySQL

before-insert.sql

mysql> use test
Database changed

mysql> ----------------------------------------
mysql> -- create table penjualan
mysql> ----------------------------------------
mysql> create table penjualan (
    ->   id int auto_increment primary key,
    ->   no_faktur varchar(50) not null
    -> )
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql> ----------------------------------------
mysql> -- create trigger before insert
mysql> ----------------------------------------
mysql> delimiter $$
mysql> create trigger trg_nomor_otomatis
    -> before insert on penjualan
    -> for each row begin
    ->   if new.no_faktur is null then
    ->     set new.no_faktur := (
    ->       select concat(curdate() + 0,
    ->         lpad(ifnull(cast(max(right(no_faktur, 3)) as unsigned integer),0) +1,4,'0')
    ->       ) from penjualan
    ->     );
    ->   end if;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> ----------------------------------------
mysql> -- coba insert satu data
mysql> ----------------------------------------
mysql> insert into penjualan (no_faktur) values (null);
Query OK, 1 row affected (0.02 sec)

mysql> ----------------------------------------
mysql> -- lihat hasilnya => SUKSES!
mysql> ----------------------------------------
mysql> select * from penjualan;
+----+--------------+
| id | no_faktur    |
+----+--------------+
|  1 | 201307220001 |
+----+--------------+
1 row in set (0.00 sec)

mysql> ----------------------------------------
mysql> -- coba satu lagi
mysql> ----------------------------------------
mysql> insert into penjualan (no_faktur) values (null);
Query OK, 1 row affected (0.02 sec)

mysql> ----------------------------------------
mysql> -- lihat hasilnya => SUKSES!
mysql> ----------------------------------------
mysql> select * from penjualan;
+----+--------------+
| id | no_faktur    |
+----+--------------+
|  1 | 201307220001 |
|  2 | 201307220002 |
+----+--------------+
2 rows in set (0.00 sec)

mysql>

Trigger sya berhasil execute. Namun jika ganti tanggal, nmor tdk kembali ke 001 ya...

Terima kasih sebelumnya sudah berbagi.

Trigger sya berhasil execute. Namun jika ganti tanggal, nmor tdk kembali ke 001 ya...

Karena memang tidak ada logic-nya pada kode di atas