TriggerM. Ammar Shadiq Ilmu Komputer Universitas Pendidikan Indonesia, Bandung 5 Mei 2008 Catatan : banyak perintah-perintah pada contoh-contoh berikut yang akan di bahas pada pembahasan mengenai data integrity.

Pastikan anda telah menginisiasikan procedural language (bahasa prosedural) pada postgreSQL yang dinamakan PL/pgsqlCREATE LANGUAGE plpgsql; CREATE TRIGGER [BEFORE | AFTER] [ INSERT | DELETE | UPDATE [OR ...]] ON FOR EACH ROW EXECUTE PROCEDURE [(args)]; BEFORE

Sebelum mengeksekusi Query pada tabel , jalankan dulu fungsi.AFTER

Setelah mengeksekusi Query pada tabel , lalu setelah itu jalankan fungsi.INSERT

Untuk Query yang memasukkan data baru pada tabel jalankan trigger.DELETE

Untuk Query yang menghapus data pada tabel jalankan trigger.UPDATE

Untuk Query yang mengubah data pada jalankan trigger.INSERT OR DELETE

Untuk Query yang memasukkan data baru atau menghapus data pada tabel jalankan trigger.INSERT OR DELETE OR UPDATE

Untuk Query yang memasukkan, menghapus atau mengubah data pada tabel jalankan trigger. Kemungkinan lainnya penggunaan OR adalah :INSERT OR UPDATE DELETE OR UPDATE

Fungsi yang dipanggil Trigger : sebuah fungsi yang di panggil trigger adalah sebuah fungsi yang tidak memiliki argumen masukan dan argumen keluarannya memiliki tipe data spesial -- TRIGGER. Fungsi yang di panggil trigger harus mengembalikan nilai NULL atau sebuah record/row yang memiliki struktur yang sama persis dengan table dimana trigger tersebut ditempatkan.

Variabel-variabel spesial trigger PostgreSQLNEW Tipe data : RECORD Variabel yang menyimpan record baru untuk operasi INSERT/UPDATE dalam trigger levelbaris(rowlevel). OLD Tipe data : RECORD Variabel ini menyimpan record lama untuk operasi UPDATE/DELETE dalam trigger levelbaris(rowlevel). TG_NAME Tipe data : name Variabel ini berisi nama trigger yang dijalankan. TG_WHEN Tipe data : text Sebuah string baik dari BEFORE atau AFTER tergantung definisi trigger. TG_OP Tipe data : text Sebuah string dari operasi INSERT,UPDATE atau DELETE yang memberitahu untuk operasi mana trigger dijalankan. (digunakan pada Contoh 2) TG_RELID Tipe data : oid Object ID dari tabel yang menyebabkan pengeksekusian trigger. TG_TABLE_NAME Tipe data : name Nama tabel yang menyebabkan pengeksekusian trigger. TG_TABLE_SCHEMA Tipe data : name Nama schema dari tabel yang menyebabkan pengeksekusian trigger. TG_NARGS Tipe data : integer Jumlah argumen yang diberikan ke procedure trigger pada statement CREATE TRIGGER TG_ARGV[] Tipe data : array yang berisi text Argumen dari statement CREATE TRIGGER. Index mulai dari 0. Pengaksesan dengan index yang salah (kurang dari 0, lebih besar dari TG_NARGS atau sama dengan TG_NARGS) akan menghasilkan nilai NULL.

Contoh 1Contoh trigger berikut ini memastikan bahwa tiap kali sebuah baris pada tabel ditambahkan (insert) atau dirubah (update), username dan waktunya pada saat itu dimasukkan kedalam baris. Trigger ini juga bekerja untuk mengecek apakah nama pegawai diisikan dan gaji bernilai positif.CREATE TABLE pegawai1 ( nama_peg text, gaji_peg integer, tgl_trkhr timestamp, user_trkhr text );

Pertama yang harus dilakukan adalah membuat fungsi yang menjelaskan apa yang dilakukan oleh trigger tersebut.CREATE FUNCTION cattn_peg() RETURNS trigger AS $cattn_peg$ BEGIN -- Periksa nama_peg dan gaji_peg tidak kosong IF NEW.nama_peg IS NULL THEN RAISE EXCEPTION 'nama_peg tidak boleh kosong'; END IF; IF NEW.gaji_peg IS NULL THEN RAISE EXCEPTION '% gaji_peg tidak boleh kosong', NEW.nama_peg; END IF; -- siap yang bekerja untuk kita jika dia yang membayar? IF NEW.gaji_peg < 0 THEN RAISE EXCEPTION '% gaji_peg tidak boleh negatif', NEW.nama_peg; END IF; -- Tuliskan siap yang mengganti Upah saat itu beserta waktunya NEW.tgl_trkhr := current_timestamp; NEW.user_trkhr := current_user; RETURN NEW; END; $cattn_peg$ LANGUAGE 'plpgsql';

Lalu membuat deklarasi trigger yang memanggil fungsi diatas.CREATE TRIGGER cattn_peg BEFORE INSERT OR UPDATE ON pegawai1 FOR EACH ROW EXECUTE PROCEDURE cattn_peg();

Contoh 2Contoh trigger berikut ini memastikan bahwa tiap kali sebuah baris pada tabel pegawai ditambahkan (insert), dirubah (update) atau dihapus (delete), disimpan catatannya (i.e. diaudit) pada tabel audit_peg. Yang disimpan adalah waktu dan user name, bersama dengan operasi yang di lakukan.CREATE TABLE pegawai2 ( nama_peg text NOT NULL, gaji_peg integer ); CREATE TABLE audit_peg1 ( operasi char(1) NOT NULL, waktu timestamp NOT NULL, userid text NOT NULL, nama_peg text NOT NULL, gaji_peg integer ); CREATE OR REPLACE FUNCTION proses_audit_peg1() RETURNS TRIGGER AS $audit_peg1$ BEGIN -----IF Buat baris (data) pada tabel audit_peg1 untuk merefleksikan operasi yang dilakukan pada tabel pegawai2, menggunakan varibel spesial TG_OP untuk mengoperasikannya.

(TG_OP = 'DELETE') THEN INSERT INTO audit_peg1 SELECT 'D', now(), user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO audit_peg1 SELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO audit_peg1 SELECT 'I', now(), user, NEW.*; RETURN NEW; END IF; RETURN NULL; -- hasil diabaikan karena trigger AFTER END; $audit_peg1$ LANGUAGE 'plpgsql'; CREATE TRIGGER audit_peg1 AFTER INSERT OR UPDATE OR DELETE ON pegawai2 FOR EACH ROW EXECUTE PROCEDURE proses_audit_peg1();

Keterangan : lihat pada fungsi diatas, baris INSERT INTO .... NEW.*

berarti ada 2 kolom yang dimasukkan oleh * yaitu nama_peg dan gaji_peg jadi sebenarnya yang dimasukkan adalah :, , , dan

Contoh 3CREATE TABLE pegawai3 ( nama_peg gaji_peg tgl_dibuat tgl_diubah gaji_peg_sblm );

TEXT NOT NULL, INTEGER, TIMESTAMP NOT NULL, TIMESTAMP, INTEGER

buatlah trigger dengan ketentuan: 1. saat data baru dimasukkan (INSERT), secara otomatis trigger harus memasukkan : tanggal data di buat (tgl_dibuat). 2. saat suatu data pegawai diubah (UPDATE), data yang harus diisikan adalah : tanggal data diubah (tgl_diubah) dan gaji pegawai sebelum diubah (gaji_peg_sblm). Jawab :CREATE OR REPLACE FUNCTION proses_ubah_gaji() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN NEW.tgl_dibuat := current_timestamp; RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN NEW.tgl_diubah := current_timestamp; NEW.gaji_peg_sblm := OLD.gaji_peg; RETURN NEW; END IF; END; $$ LANGUAGE 'plpgsql';

CREATE TRIGGER ubah_pegawai BEFORE INSERT OR UPDATE ON pegawai3 FOR EACH ROW EXECUTE PROCEDURE proses_ubah_gaji(); Anda dapat mencobanya dengan perintah : Insert :-- masukkan data pegawai baru dengan nama shadiq dan gaji Rp. 500.000 INSERT INTO pegawai3 (nama_peg, gaji_peg) VALUES ('shadiq', 500000); -- Lalu lihat data pegawai shadiq yang telah di masukkan SELECT * FROM pegawai3 WHERE nama_peg = 'shadiq';

Update :

-- ubah gaji pegawai shadiq menjadi Rp. 1.000.000 UPDATE pegawai3 SET gaji_peg = 1000000 WHERE nama_peg = 'shadiq'; -- Lalu lihat data pegawai shadiq yang telah di ubah SELECT * FROM pegawai3 WHERE nama_peg = 'shadiq';

Contoh 4CREATE TABLE pegawai4 ( nama_peg text NOT NULL, gaji integer ); CREATE TABLE log_pegawai4 ( nama_peg text NOT NULL, operasi text NOT NULL, tgl_dibuat timestamp, tgl_diubah timestamp, gaji_peg_sekarang integer, gaji_peg_sebelumnya integer, selisih_gaji integer ); buat lah trigger dengan ketentuan : 1. saat meng-insert tabel pegawai4 isi tabel pegawai4_properties dengan nilai :nama_peg operasi tgl_dibuat gaji_peg_sekarang : : : : 'Insert'

2. saat meng-update tabel pegawai4 isi tabel pegawai4_properties dengan nilainama_peg operasi tgl_diubah gaji_peg_sebelumnya gaji_peg_sekarang selisih_gaji : : : : : : 'Update' [nilainya selalu positif]

Jawab :CREATE OR REPLACE FUNCTION isi_properties_pegawai4() RETURNS TRIGGER AS $$ BEGIN IF(TG_OP = 'INSERT') THEN INSERT INTO log_pegawai4 (nama_peg, VALUES (NEW.nama_peg, TG_OP, NOW(), RETURN NEW; ELSIF(TG_OP = 'UPDATE') THEN INSERT INTO log_pegawai4 (nama_peg, gaji_peg_sekarang, selisih_gaji) VALUES (NEW.nama_peg, TG_OP, NOW(), OLD.gaji)); RETURN NEW; END IF; END; $$ LANGUAGE 'plpgsql'; operasi, tgl_dibuat, gaji_peg_sekarang) NEW.gaji);

operasi, tgl_diubah, gaji_peg_sebelumnya, OLD.gaji, NEW.gaji , ABS(NEW.gaji-

CREATE TRIGGER trg_isi_prop_peg4 BEFORE INSERT OR UPDATE ON pegawai4 FOR EACH ROW EXECUTE PROCEDURE isi_properties_pegawai4();

TG_OPFariabel spesial TG_OP pada PostgreSQL merefleksikan operasi apa yang dilakukan pada suatu tabel. Namun apa yang terjadi bila berdasarkan kebijakan perusahaan, System Data Base diganti? Tentunya Trigger-trigger yang telah kita buat belum tentu dapat digunakan lagi, karena pada DBMS lain belum tentu ada variabel TG_OP pada trigger. solusinya : anda dapat membuat trigger untuk masingmasing operasi, tentu jumlah trigger menjadi banyak, namun dibayar dengan dipecahkannya permasalahan kompatibilitas dengan DBMS jenis lain. Ex :CREATE TRIGGER trg1_insert BEFORE INSERT ON tabelku FOR EACH ROW EXECUTE PROCEDURE fungsi1_insert(); CREATE TRIGGER trg2_update BEFORE UPDATE ON tabelku FOR EACH ROW EXECUTE PROCEDURE fungsi2_update(); CREATE TRIGGER trg2_delete BEFORE DELETE ON tabelku FOR EACH ROW EXECUTE PROCEDURE fungsi3_delete();