1
BAB VI TRIGGER 6.1 DEFINISI TRIGGER
Trigger adalah sebuah objek database yang diasosiasikan dengan sebuah tabel dan akan aktif (terpicu/trigger) (terpicu/trig ger) ketika sebuah event terjadi pada tabel tersebut Trigger hanya terjadi ketika ada eksekusi INSERT, DELETE dan UPDATE pada tabel yang bersangkutan. Waktu eksekusi trigger yang mungkin terjadi terdiri dari 2 yaitu BEFORE dan AFTER dari statement SQL-nya •
•
•
6.2 KEUNTUNGAN MENGGUNAKAN TRIGGER •
•
Trigger dapat digunakan untuk mengubah data sebelum proses INSERT dilakukan atau untuk memberikan nilai default. Misal mengubah data yang diluar nilai yang diperbolehkan. Misalnya, jika ada pengisian nilai diatas 100, maka akan dijadikan 100. Anda dapat dapat menyimpan data data suatu record ke tabel tabel lain (misalnya (misalnya history) history) sebelum data data tersebut diupdate atau didelete. Sehingga semua perubahan data dapat terlacak dari sejak data itu dibuat.
6. 3 STRUKTUR TRIGGER CREATE TRINGGER nama_trigger { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON nama_tabel FOR EACH ROW Statement-statement 6.4 MENGAKSES NILAI BARU DAN LAMA •
Didalam trigger, anda dapat mengakses data lama dan baru. Data lama dapat direference dengan record OLD dan data baru dapat dideference dengan record NEW. OPERASI
•
NEW (READ/WRITE) √ √
OLD (READ)
INSERT UPDATE √ DELETE √ Untuk mengacu ke sebuah field dapat ditulis dengan NEW.namafield atau OLD.namafield.
CONTOH 1 •
•
Ada sebuah sebuah database database dbAkdemik yang yang mempunyai mempunyai tabel mahasiswa mahasiswa {nim, nama, nama, alamat}. Buatlah sebuah trigger yang akan menyimpan history alamat. Jika sebuah alamat berubah, maka alamat lama harus disimpan ke tabel history_alamat_mhs.
JAWAB : •
Persiapan awal mysql> mysql> mysql> -> -> -> mysql> -> -> -> mysql> -> -> -> ->
CREATE CREATE DATABASE DATABASE dbAkademik; dbAkademik; USE USE dbAkademik; dbAkademik; CREATE CREATE TABLE TABLE mhs( mhs( Nim VARCHAR(10) PRIMARY KEY, Nama VARCHAR(50) Not Null, Alamat VARCHAR(200) Not Null); INSERT INSERT INTO INTO mhs values ('09.53.0042','Septario Sagita','Jl. aa No.1'), ('09.53.0045','Tanto Taufik N','Jl. bb No.2'), ('09.53.0062','Irfan Fajarudy','Jl. cc No.3'); CREATE CREATE TABLE TABLE history_ala history_alamat_mhs mat_mhs( ( Waktu datetime, Nim VARCHAR(8), Alamat VARCHAR(200), Oleh VARCHAR(50));
2 •
Pembuatan trigger mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_upd trig_update_mhs ate_mhs // Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> CREATE CREATE TRIGGER TRIGGER trig_upda trig_update_mhs te_mhs -> AFTER UPDATE ON mhs -> FOR EACH ROW -> BEGIN -> INSERT INTO history_alamat_mhs -> VALUES (now(), OLD.nim, OLD.alamat, USER()); -> END// Query OK, 0 rows affected (0.09 sec) mysql> DELIMITE DELIMITER R ;
•
Contoh penggunaan trigger mysql> UPDATE UPDATE mhs mhs SET alamat='D alamat='Ds. s. Lembah Lembah Jetis Rt 32/11 Madiun' -> WHERE nim='09.53.0042'; mysql> UPDATE UPDATE mhs mhs SET alamat='J alamat='Jl. l. Setiyasa Setiyasa 12 Madiun' -> WHERE nim='09.53.0045'; mysql> UPDATE UPDATE mhs mhs SET alamat='J alamat='Jl. l. Ronggo Ronggo Pustoko Pustoko Rt 06 Rw 03 Madiun' Madiun' -> WHERE nim='09.53.0062'; mysql> select select * from mhs; mhs; +------------+-----------------+---------------------------------------+ | Nim | Nama | Alamat | +------------+-----------------+---------------------------------------+ | 09.53.0042 | Septario Sagita | Ds. Lembah Jetis Rt 32/11 Madiun | | 09.53.0045 | Tanto taufik N | Jl. Setiyasa 12 Madiun | | 09.53.0062 | Irfan Fajarufy | Jl. Ronggo Pustoko Rt 06 Rw 03 Madiun | +------------+-----------------+---------------------------------------+ mysql> select select * from history_al history_alamat_mh amat_mhs; s; +---------------------+------------+-------------+----------------+ | Waktu | Nim | Alamat | Oleh | +---------------------+------------+-------------+----------------+ | 2010-10-19 03:41:43 | 09.53.0042 | Jl. aa No.1 | root@localhost | | 2010-10-19 03:42:32 | 09.53.0045 | Jl. bb No.2 | root@localhost | | 2010-10-19 03:43:13 | 09.53.0062 | Jl. cc No.3 | root@localhost | +---------------------+------------+-------------+----------------+
•
Untuk melihat semua alamat yang pernah digunakan mhs yang bernim ’09.53.0042’ adalah : mysql> (SELECT (SELECT now() waktu, waktu, alamat alamat FROM FROM mhs WHERE WHERE nim='09.53. nim='09.53.0042') 0042') -> UNION -> (SELECT waktu,alamat FROM history_alamat_mhs WHERE nim='09.53.0042') -> ORDER BY waktu DESC; +---------------------+----------------------------------+ | waktu | alamat | +---------------------+----------------------------------+ | 2010-10-19 04:00:05 | Ds. Lembah Jetis Rt 32/11 Madiun | | 2010-10-19 03:41:43 | Jl. aa No.1 | +---------------------+----------------------------------+
•
Trigger pertama mempunyai kekurangan yaitu ketika ada perubahan di tabel mhs walaupun tidak mengubah kolom alamat, maka statement INSERT di tabel history akan dijalankan. Trigger ini bisa dioptimalkan dengan membuat trigger seperti dibawah ini : mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_upd trig_update_mhs ate_mhs // mysql> CREATE CREATE TRIGGER TRIGGER trig_upda trig_update_mhs te_mhs -> AFTER UPDATE ON mhs -> FOR EACH ROW -> BEGIN -> IF OLD.alamat <> NEW.alamat THEN -> INSERT INTO history_alamat_mhs -> VALUES(now(),OLD.nim,OLD.alamat,USER()); -> END IF; -> END // Query OK, 0 rows affected (0.03 sec) mysql> DELIMITE DELIMITER R ;
3
CONTOH 2 •
Membuat sebuah trigger yang akan dieksekusi ketika ada perubahan NIM di tabel mhs yang akan melakukan update ke tabel history_alamat_mhs untuk menyesuaikan NIM-nya agar relasinya tidak terlepas. mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_upd trig_update_nim ate_nim // mysql> CREATE CREATE TRIGGER TRIGGER trig_upda trig_update_nim te_nim -> AFTER UPDATE ON mhs -> FOR EACH ROW -> BEGIN -> IF OLD.nim <> NEW.nim THEN -> UPDATE history_alamat_mhs SET nim=NEW.nim WHERE nim=OLD.nim; -> END IF; -> END// ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
•
Jika trigger tersebut dieksekusi maka akan terlihat ada peringatan berisi : “ This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table” yang artinya anda tidak bisa membuat trigger pada sebuah tabel pada waktu dan event
•
yang sama. Solusi yang bisa dilakukan adalah menggabung isi trigger trig_update_mhs dengan isi trigger yang baru
mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_upd trig_update_mhs ate_mhs // mysql> CREATE CREATE TRIGGER TRIGGER trig_upda trig_update_mhs te_mhs -> AFTER UPDATE ON mhs -> FOR EACH ROW -> BEGIN -> IF OLD.alamat <> NEW.alamat THEN -> INSERT INTO history_alamat_mhs -> VALUES(now(),OLD.nim,OLD.alamat,USER()); -> END IF; -> IF OLD.nim <> NEW.nim THEN -> UPDATE history_alamat_mhs SET nim=NEW.nim WHERE nim=OLD.nim; -> END IF; -> END// Query OK, 0 rows affected (0.00 sec) mysql> DELIMITE DELIMITER R ; • Pengunaan trigger trig_update_mhs yang baru : mysql> SELECT SELECT * FROM history_al history_alamat_mh amat_mhs; s; +---------------------+------------+------------------------+----------------+ | Waktu | Nim | Alamat | Oleh | +---------------------+------------+------------------------+----------------+ | 2010-10-19 03:41:43 | 09.53.0042 | Jl. aa No.1 | root@localhost | | 2010-10-19 03:42:32 | 09.53.0045 | Jl. bb No.2 | root@localhost | | 2010-10-19 03:43:13 | 09.53.0062 | Jl. cc No.3 | root@localhost | | 2010-10-19 03:50:41 | 09.53.0045 | Jl. Setiyasa 12 Madiun | root@localhost | +---------------------+------------+------------------------+----------------+
mysql> UPDATE UPDATE mhs mhs SET nim='09.5 nim='09.53.1045' 3.1045' WHERE nim='09.5 nim='09.53.0045' 3.0045'; ; mysql> SELECT SELECT * FROM history_al history_alamat_mh amat_mhs; s; +---------------------+------------+------------------------+----------------+ | Waktu | Nim | Alamat | Oleh | +---------------------+------------+------------------------+----------------+ | 2010-10-19 03:41:43 | 09.53.0042 | Jl. aa No.1 | root@localhost | | 2010-10-19 03:42:32 | 09.53.1045 | Jl. bb No.2 | root@localhost | | 2010-10-19 03:43:13 | 09.53.0062 | Jl. cc No.3 | root@localhost | | 2010-10-19 03:50:41 | 09.53.1045 | Jl. Setiyasa 12 Madiun | root@localhost | +---------------------+------------+------------------------+----------------+ 4 rows in set (0.00 sec)
CONTOH 3 •
Membuat sebuah trigger yang akan menghapus semua data pada tabel history_alamat_mhs ketika ada penghapusan pada tabel mhs. mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_del trig_delete_mhs ete_mhs //
4 mysql> CREATE CREATE TRIGGER TRIGGER trig_dele trig_delete_mhs te_mhs -> AFTER DELETE ON mhs -> FOR EACH ROW -> BEGIN -> DELETE FROM history_alamat_mhs WHERE nim=OLD.nim; -> END// Query OK, 0 rows affected (0.63 sec) mysql> DELIMITE DELIMITER R ; •
Penggunaan trigger trig_delete_mhs mysql> SELECT SELECT * FROM history_al history_alamat_mh amat_mhs; s; +---------------------+------------+------------------------+----------------+ | Waktu | Nim | Alamat | Oleh | +---------------------+------------+------------------------+----------------+ | 2010-10-19 03:41:43 | 09.53.0042 | Jl. aa No.1 | root@localhost | | 2010-10-19 03:42:32 | 09.53.1045 | Jl. bb No.2 | root@localhost | | 2010-10-19 03:43:13 | 09.53.0062 | Jl. cc No.3 | root@localhost | | 2010-10-19 03:50:41 | 09.53.1045 | Jl. Setiyasa 12 Madiun | root@localhost | +---------------------+------------+------------------------+----------------+ mysql> SELECT SELECT * FROM mhs; mhs; +------------+-----------------+---------------------------------------+ | Nim | Nama | Alamat | +------------+-----------------+---------------------------------------+ | 09.53.0042 | Septario Sagita | Ds. Lembah Jetis Rt 32/11 Madiun | | 09.53.0062 | Irfan Fajarufy | Jl. Ronggo Pustoko Rt 06 Rw 03 Madiun | | 09.53.1045 | Tanto taufik N | Jl. Setiyasa 12a Madiun | +------------+-----------------+---------------------------------------+ mysql> DELETE DELETE FROM FROM mhs where nim='09.5 nim='09.53.1045'; 3.1045'; Query OK, 1 row affected (0.68 sec) mysql> SELECT SELECT * FROM mhs; mhs; +------------+-----------------+---------------------------------------+ | Nim | Nama | Alamat | +------------+-----------------+---------------------------------------+ | 09.53.0042 | Septario Sagita | Ds. Lembah Jetis Rt 32/11 Madiun | | 09.53.0062 | Irfan Fajarufy | Jl. Ronggo Pustoko Rt 06 Rw 03 Madiun | +------------+-----------------+---------------------------------------+ mysql> SELECT SELECT * FROM history_al history_alamat_mh amat_mhs; s; +---------------------+------------+-------------+----------------+ | Waktu | Nim | Alamat | Oleh | +---------------------+------------+-------------+----------------+ | 2010-10-19 03:41:43 | 09.53.0042 | Jl. aa No.1 | root@localhost | | 2010-10-19 03:43:13 | 09.53.0062 | Jl. cc No.3 | root@localhost | +---------------------+------------+-------------+----------------+
CONTOH 4
Membuat sebuah trigger yang mencegah perubahan pada primary key tabel mhs (field nim). Jika ada perubahan, maka nim tidak boleh berubah. (agak kontradiksi dengan contoh 2, tapi ngak masalah. Sekedar contoh). Hal ini dapat dilakukan yaitu dengan mengeset nilai nim yang baru (NEW.nim) dengan nilai yang lama (OLD.nim)
•
mysql> DELIMITE DELIMITER R // mysql> DROP DROP TRIGGER TRIGGER IF EXISTS trig_upd trig_update_nim_ ate_nim_mhs mhs // mysql> CREATE CREATE TRIGGER TRIGGER trig_upda trig_update_nim_ te_nim_mhs mhs -> BEFORE UPDATE ON mhs -> FOR EACH ROW -> BEGIN -> SET NEW.nim=OLD.nim; -> END // Query OK, 0 rows affected (0.01 sec) mysql> DELIMITE DELIMITER R ; •
Penggunaan trigger trig_update_nim_mhs mysql> SELECT SELECT * FROM mhs; mhs; +------------+-----------------+---------------------------------------+ | Nim | Nama | Alamat | +------------+-----------------+---------------------------------------+ | 09.53.0042 | Septario Sagita | Ds. Lembah Jetis Rt 32/11 Madiun | | 09.53.0062 | Irfan Fajarufy | Jl. Ronggo Pustoko Rt 06 Rw 03 Madiun | +------------+-----------------+---------------------------------------+ mysql> UPDATE UPDATE mhs mhs SET nim='09.5 nim='09.53.1042' 3.1042' WHERE nim='09.5 nim='09.53.0042' 3.0042'; ; Query OK, 0 rows affected (0.00 sec)
5 mysql> SELECT SELECT * FROM mhs; mhs; +------------+-----------------+---------------------------------------+ | Nim | Nama | Alamat | +------------+-----------------+---------------------------------------+ | 09.53.0042 | Septario Sagita | Ds. Lembah Jetis Rt 32/11 Madiun | | 09.53.0062 | Irfan Fajarufy | Jl. Ronggo Pustoko Rt 06 Rw 03 Madiun | +------------+-----------------+---------------------------------------+
TUGAS ..!
Buatlah database dbTransfer yang mempunyai tabel Rekening dan tabel Transfer. Tabel Rekening mempunyai field : No Int Primary Key, Nama Varchar(30) dan Saldo Double. Tabel Transfer untuk menyimpan data transaksi transfer mempunyai field : - NoTransaksi : Int Auto_Increment Primary Key - WaktuTransaksi : DateTime - NoRekPengirim : Int - NoRekPenerima : Int - Be BesarTransfer : Double mempunyai mempunyai constrain c onstraintt foreign key sbb: • • •
CONSTRAINT FK_Transfer1 FOREIGN KEY(NoRekPengirim) REFERENCES rekening(No), CONSTRAINT FK_Transfer2 FOREIGN KEY(NoRekPenerima) REFERENCES rekening(No);
Buatlah trigger, jika ada penambahan data di tabel transfer (AFTER INSERT ON transfer), maka akan mengupdate saldo pada rekening yang bersangkutan sesuai dengan BesarTransfer. Mirip dengan stored procedure Transfer.
•
Setelah dibuat ikuti langkah-langkah berikut : 1) Isi kan data ke tabel rekening sbb: mysql> INSERT INTO INTO rekening(no rekening(no,nama,s ,nama,saldo) aldo) values(1,' values(1,'Irfan', Irfan',1000000) 1000000), , (2,'Tanto',2000000);
2) Percobaan ke-1 trigger, dengan mengisikan data ke tabel rekening sbb: mysql> INSERT INTO INTO transfer(Wa transfer(WaktuTran ktuTransaksi,No saksi,NorekPengi rekPengirim, rim, NorekPener NorekPenerima,Besa ima,BesarTransf rTransfer) er) values('2010-11-02',1,2,100000); mysql> select * from transfer; transfer; +-------------+---------------------+---------------+---------------+---------------+ | NoTransaksi | WaktuTransaksi | NoRekPengirim | NoRekPenerima | BesarTransfer | +-------------+---------------------+---------------+---------------+---------------+ | 1 | 2010-11-02 00:00:00 | 1 | 2 | 100000 | +-------------+---------------------+---------------+---------------+---------------+
3) Hasil dari percobaan ke-1 trigger, maka rekening no 1 akan berkurang 100.000 menjadi 900.000 sedangkan rekening no 2 akan bertambah 100.000 menjadi 2.100.000: mysql> select * from rekening; rekening; +----+-------+---------+ | No | Nama | Saldo | +----+-------+---------+ | 1 | Irfan | 900000 | | 2 | Tanto | 2100000 | +----+-------+---------+
Percobaan trigger dengan melakukan transfer untuk rekening no 3 maka akan terjadi peringatan error Foreign Key karena rekening no 3 tidak ada di tabel rekening : 4)
mysql> INSERT INTO INTO transfer(Wa transfer(WaktuTran ktuTransaksi,No saksi,NorekPengi rekPengirim,Nor rim,NorekPeneri ekPenerima,Besar ma,BesarTransfe Transfer) r) values('2010-11-02',1,3,100000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbtransfer/transfer`, CONSTRAINT `FK_Transfer2` FOREIGN KEY (`NoRekPenerima`) REFERENCES `rekening` (`No`))
5) Percobaan ke-2 trigger, dengan mengisikan data ke tabel transfer sbb: mysql> INSERT INTO INTO transfer(Wa transfer(WaktuTran ktuTransaksi,No saksi,NorekPengi rekPengirim,Nor rim,NorekPeneri ekPenerima,Besar ma,BesarTransfe Transfer) r) values('2010-11-02',1,2,150000); mysql> select * from transfer; transfer; +-------------+---------------------+---------------+---------------+---------------+ | NoTransaksi | WaktuTransaksi | NoRekPengirim | NoRekPenerima | BesarTransfer | +-------------+---------------------+---------------+---------------+---------------+ | 1 | 2010-11-02 00:00:00 | 1 | 2 | 100000 | | 2 | 2010-11-02 00:00:00 | 1 | 2 | 150000 | +-------------+---------------------+---------------+---------------+---------------+
6) Hasil dari percobaan ke-2 trigger, maka rekening no 1 akan berkurang 150.000 menjadi 750.000 sedangkan rekening no 2 akan bertambah 150.000 menjadi 2.250.000:
6 mysql> select * from rekening; rekening; +----+-------+---------+ | No | Nama | Saldo | +----+-------+---------+ | 1 | Irfan | 750000 | | 2 | Tanto | 2250000 | +----+-------+---------+
