Pengantar MySQL Trigger dan Cara Menggunakannya

Image by Oxana Melis On Unsplash

{tocify} $title={Daftar Isi}

Pendahuluan

Saat menginput data pada suatu tabel mungkin suatu waktu pernah terfikirkan oleh kita bagaimana untuk menyalin data ke tabel lain sebagai cadangan atau sebagai sebuah catatan log jika suatu saat data tersebut berubah atau terhapus secara tidak sengaja? Kita bisa saja menyalin data dari suatu tabel ke tabel lain menggunakan statement insert into select namun bukan itu yang penulis maksud, melainkan saat kita menginput data ke tabel A maka saat bersamaan data tersebut juga secara otomatis akan tersimpan ke tabel B atau ketika data pada tabel A berubah maka secara otomatis akan menyimpan data lama ke tabel B. MySQL telah menyediakan fungsi tersebut yang biasa dikenal sebagai Trigger yang akan merekam data secara otomatis ketika ada aktivitas manipulasi data (DML) seperti insert, update, ataupun delete sehingga biasa dikenal juga sebagai DML Trigger.

MySQL Trigger adalah kode prosedural yang dijalankan secara otomatis karena adanya syarat dan kondisi untuk menanggapi query dml sebelum atau setelah manipulasi data dilakukan karena trigger memang hanya memiliki dua kondisi yakni BEFORE atau AFTER, dan menggunakan keyword OLD atau NEW sebagai parameter data yang akan disimpan, adapun bentuk umum / format dasar trigger adalah sebagai berikut:

Format:

DELIMITER $$
Create trigger nama_trigger [BEFORE|AFTER INSERT/UPDATE/DELETE] on asal_tabel for each row
Begin
Insert into log_tabel set log_tabel_kolom1 = [OLD|NEW].asal_tabel_kolom1
log_tabel_kolomN = [OLD|NEW].asal_tabel_kolomN;
END$$
DELIMITER ;

Sebagai contoh soal penulis menggunakan tabel mahasiswa dan update_mahasiswa sebagai berikut:

mysql> desc mahasiswa;
+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| id_mhs    | int           | NO   | PRI | NULL    | auto_increment |
| nama      | varchar(45)   | YES  |     | NULL    |                |
| npm       | char(8)       | YES  | UNI | NULL    |                |
| j_kelamin | enum('L','P') | YES  |     | NULL    |                |
| t_lahir   | date          | YES  |     | NULL    |                |
| alamat    | varchar(50)   | YES  |     | NULL    |                |
| kota      | varchar(45)   | YES  |     | NULL    |                |
| telepon   | varchar(12)   | YES  |     | NULL    |                |
| email     | varchar(50)   | YES  |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> desc log_mahasiswa;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id_log       | int          | NO   | PRI | NULL    | auto_increment |
| id_mhs       | int          | YES  |     | NULL    |                |
| npm          | char(8)      | YES  |     | NULL    |                |
| alamat_lama  | varchar(100) | YES  |     | NULL    |                |
| kota_lama    | varchar(50)  | YES  |     | NULL    |                |
| telepon_lama | varchar(45)  | YES  |     | NULL    |                |
| email_lama   | varchar(50)  | YES  |     | NULL    |                |
| update_at    | timestamp    | YES  |     | NULL    |                |
| register_at  | timestamp    | YES  |     | NULL    |                |
| deleted_at   | timestamp    | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
10 rows in set (0.07 sec)

Trigger Insert

Trigger insert akan secara otomatis menambahkan data ke log tabel setelah query insert dijalankan dan hanya bisa menggunakan keyword new, berikut contohnya:

Contoh:

delimiter $$
Create trigger insert_mahasiswa after insert on mahasiswa for each row
Begin
insert into log_mahasiswa set id_mhs = new.id_mhs,
npm = new.npm,
register_at = NOW();
end$$
delimiter ;

Sebelum Insert Data:

mysql> select * from mahasiswa;
Empty set (0.01 sec)

mysql> select * from log_mahasiswa;
Empty set (0.07 sec)

Setelah Insert Data:

mysql> select id_mhs, npm from mahasiswa;
+--------+----------+
| id_mhs | npm      |
+--------+----------+
|      3 | 51411111 |
|      4 | 51411112 |
|      5 | 51411113 |
|      1 | 51411136 |
|      2 | 51412371 |
+--------+----------+
5 rows in set (0.00 sec)

mysql> select id_mhs, npm, register_at from log_mahasiswa;
+--------+----------+---------------------+
| id_mhs | npm      | register_at         |
+--------+----------+---------------------+
|      1 | 51411136 | 2021-11-25 14:58:13 |
|      2 | 51412371 | 2021-11-25 14:58:13 |
|      3 | 51411111 | 2021-11-25 14:58:13 |
|      4 | 51411112 | 2021-11-25 14:58:13 |
|      5 | 51411113 | 2021-11-25 14:58:13 |
+--------+----------+---------------------+
5 rows in set (0.00 sec)

mysql> select alamat_lama, kota_lama, telepon_lama from log_mahasiswa;
+-------------+-----------+--------------+
| alamat_lama | kota_lama | telepon_lama |
+-------------+-----------+--------------+
| NULL        | NULL      | NULL         |
| NULL        | NULL      | NULL         |
| NULL        | NULL      | NULL         |
| NULL        | NULL      | NULL         |
| NULL        | NULL      | NULL         |
+-------------+-----------+--------------+
5 rows in set (0.00 sec)

Dari contoh diatas hanya kolom id_mhs dan npm yang akan menyalin data ke tabel log_mahasiswa sedangkan kolom lainnya bernilai NULL, selain itu ada beberapa hal yang tidak bisa dilakukan ketika menggunakan trigger untuk insert:

Tidak bisa menggunakan keyword OLD untuk insert
Idealnya menggunakan kondisi after walau bisa juga menggunakan kondisi before akan tetapi jika kolom pada tabel asal memiliki atribut auto_increment, maka akan bernilai 0 untuk tabel trigger

Trigger Update

Trigger untuk update data dapat menggunakan keyword old dan new bergantung pada kondisi before atau after yang ditentukan, berikut contoh trigger ketika hendak melakukan update data:

mysql> DELIMITER $$
mysql> CREATE TRIGGER update_mahasiswa before update on mahasiswa for each row
    -> BEGIN
    -> insert into log_mahasiswa set id_mhs = old.id_mhs,
    -> npm = old.npm,
    -> telepon_lama = old.telepon,
    -> update_at = NOW();
    -> end$$
Query OK, 0 rows affected (0.27 sec)
mysql> DELIMITER ;

Nomor telepon sebelum terupdate:

mysql> select telepon from mahasiswa where id_mhs = 1;
+--------------+
| telepon      |
+--------------+
| 089611111111 |
+--------------+
1 row in set (0.07 sec)

Setelah terupdate:

mysql> update mahasiswa set telepon = 08123455610 where id_mhs = 1;
Query OK, 1 row affected (0.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select telepon from mahasiswa where id_mhs = 1;
+------------+
| telepon    |
+------------+
| 8123455610 |
+------------+
1 row in set (0.00 sec)

mysql> select id_mhs, npm, telepon_lama from log_mahasiswa where id_mhs = 1;
+--------+----------+--------------+
| id_mhs | npm      | telepon_lama |
+--------+----------+--------------+
|      1 | 51411136 | NULL         |
|      1 | 51411136 | 089611111111 |
+--------+----------+--------------+
2 rows in set (0.00 sec)

Berbeda dari contoh insert sebelumnya bahwa kolom telepon_lama akan menyimpan nomor telepon lama dari mahasiswa jika suatu waktu melakukan perubahan data hal tersebut dikarenakan menggunakan keyword OLD, sedangkan kolom lainnya yang tidak disebutkan dalam instruksi trigger tidak memiliki nilai alias NULL, contohnya sebagai berikut:

mysql> update mahasiswa set kota = "Solo" where id_mhs = 1;
Query OK, 1 row affected (0.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id_mhs, npm, kota, telepon, email from mahasiswa where id_mhs = 1;
+--------+----------+------+
| id_mhs | npm      | kota |
+--------+----------+------+
|      1 | 51411136 | Solo |
+--------+----------+------+
1 row in set (0.00 sec)

mysql> select id_mhs, npm, alamat_lama, kota_lama, telepon_lama, email_lama from log_mahasiswa where id_mhs = 1;
+--------+----------+-------------+-----------+--------------+------------+
| id_mhs | npm      | alamat_lama | kota_lama | telepon_lama | email_lama |
+--------+----------+-------------+-----------+--------------+------------+
|      1 | 51411136 | NULL        | NULL      | NULL         | NULL       |
|      1 | 51411136 | NULL        | NULL      | 089611111111 | NULL       |
|      1 | 51411136 | NULL        | NULL      | 089611111111 | NULL       |
+--------+----------+-------------+-----------+--------------+------------+
3 rows in set (0.00 sec)

Trigger Delete

Trigger delete adalah kebalikan dari insert dimana pada insert hanya dapat menggunakan keyword new, sementara pada delete hanya dapat menggunakan keyword old, contohnya sebagai berikut:

mysql> delimiter $$
mysql> create trigger delete_mahasiswa before delete on mahasiswa for each row
    -> begin
    -> insert into log_mahasiswa set id_mhs = old.id_mhs,
    -> npm = old.npm,
    -> alamat_lama = old.alamat,
    -> kota_lama = old.kota,
    -> telepon_lama = old.telepon,
    -> email_lama = old.email,
    -> deleted_at = NOW();
    -> end$$
Query OK, 0 rows affected (0.38 sec)
mysql> delimiter ;

mysql> delete from mahasiswa where id_mhs = 1;
Query OK, 1 row affected (0.17 sec)

Dan hasilnya sebagai berikut:

Lanjutan: Update satu atau beberapa kolom sekaligus

Pada contoh trigger update sebelumnya penulis hanya melakukan update pada satu kolom saja yakni telepon sehingga nomor telepon sebelumnya akan tersimpan di tabel log_mahasiswa, namun bagaimana jika suatu waktu ada lebih dari satu kolom yang perlu di update, bisakah menyimpan semuanya dengan trigger? Jawabannya bisa, dengan menggunakan kondisi IF..THEN, contohnya sebagai berikut:

mysql> DELIMITER $$
mysql> create trigger update_banyak_mahasiswa before update on mahasiswa for each row
    -> begin
    -> if (NEW.alamat != OLD.alamat) then
    -> insert into log_mahasiswa
    -> (id_mhs, npm, alamat_lama, update_at)
    -> values
    -> (old.id_mhs, old.npm, concat(old.alamat, " | " ,new.alamat), NOW());
    -> END IF;
    -> if (NEW.kota != OLD.kota) then
    -> insert into log_mahasiswa
    -> (id_mhs, npm, kota_lama, update_at)
    -> values
    -> (old.id_mhs, old.npm, concat(old.kota, " | " ,new.kota), NOW());
    -> END IF;
    -> if (NEW.telepon != OLD.telepon) then
    -> insert into log_mahasiswa
    -> (id_mhs, npm, telepon_lama, update_at)
    -> values
    -> (old.id_mhs, old.npm, concat(old.telepon, " | " ,new.telepon), NOW());
    -> END IF;
    -> if (NEW.email != OLD.email) then
    -> insert into log_mahasiswa
    -> (id_mhs, npm, email_lama, update_at)
    -> values
    -> (old.id_mhs, old.npm, concat(old.email, " | " ,new.email), NOW());
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.35 sec)
mysql> DELIMITER ;

mysql> select id_mhs, npm, kota, telepon, email from mahasiswa where id_mhs = 2;
+--------+----------+--------+--------------+--------------------+
| id_mhs | npm      | kota   | telepon      | email              |
+--------+----------+--------+--------------+--------------------+
|      2 | 51412371 | Bekasi | 081212312312 | aditya123@mail.com |
+--------+----------+--------+--------------+--------------------+
1 row in set (0.13 sec)

mysql> select id_mhs, npm, kota_lama, telepon_lama, email_lama from log_mahasiswa where id_mhs = 2;
+--------+----------+-----------+--------------+------------+
| id_mhs | npm      | kota_lama | telepon_lama | email_lama |
+--------+----------+-----------+--------------+------------+
|      2 | 51412371 | NULL      | NULL         | NULL       |
+--------+----------+-----------+--------------+------------+
1 row in set (0.04 sec)
mysql> update mahasiswa set alamat = "Jl. Pamungkas 1", Kota = "Jakarta Timur", telepon = "081211111111", email = "aditya1616@mail.com" where id_mhs = 2;
Query OK, 1 row affected (0.40 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Berikut adalah hasil akhirnya:

Dari hasil diatas data terinput sebanyak tiga kali dikarenakan ada tiga kolom yang di update, setiap kolom yang terupdate akan terinput juga pada tabel log_mahasiswa dan penulis juga memisahkan data lama dan baru dengan menggunakan fungsi concat.