Praktikum
6 TRIGGER Tujuan : 1. Mengetahui dan memahami trigger dalam Oracle 2. Mengerti manfaat dan batasan penggunaan trigger 3. Mampu mengimplementasikan trigger dalam Oracle Alat dan Bahan : 1. Buku literatur mengenai PL/SQL ORACLE, terutama yang menggunakan DBMS ORACLE 10g. 2. Modul praktikum BASIS DATA LANJUT 3. Modul praktikum DATA MINING DAN WAREHOUSING Landasan Teori : 1. Definisi Merupakan blok PL/SQL yang memiliki fungsi hampir sama dengan procedure maupun function, hanya saja, jika procedure atau function dijalankan secara eksplisit lewat pemanggilan procedure atau function tersebut, sedangkan untuk trigger, dijalankan secara implisit melalui perintah insert, delete atau update. 2. Manfaat Manfaat trigger: a. Mengatur integritas dari constraint yang kompleks yang tidak mungkin ditangani oleh sintaks-sintaks pembuatan tabel. b. Mencegah transaksi yang tidak valid c. Memperbaiki keamanan database dengan menyediakan audit yang lebih kompleks mengenai informasi perubahan database dan user siapa yang melakukan perubahan. d. Secara otomatis memberi sinyal program lain untuk melakukan sesuatu jika isi tabel diubah. 3. Batasan Trigger mempunyai batasan a. Tidak dapat menggunakan perintah commit dan rollback, selain itu juga tidak dapat memanggil procedure, function atau package yang menggunakan perintah commit dan rollback. b. Tidak dapat diimplementasikan pada kolom pada suatu tabel yang memilki constraint, jika pada akhirnya akan menyebabkan pelanggaran constraint. Biasanya terjadi akibat modifikasi pada primary key
4. Struktur Dasar Trigger SINTAKS CREATE [or replace] TRIGGER nama_trigger ( BEFORE|AFTER ) ( INSERT|UPDATE [OF nama_kolom,..]|DELETE )ON nama_tabel [FOR EACH ROW] [WHEN (kondisi)] DECLARE Deklarasi tipe data BEGIN Trigger body END;
Contoh: CREATE OR REPLACE TRIGGER bef_upd_row_keuangan BEFORE Trigger Timing UPDATE OF donasi ON keuangan Trigger Event FOR EACH ROW Trigger Type WHEN (new.donasi => 0) Trigger Restriction BEGIN Trigger DBMS_OPUTPUT.PUT('Donasi terbaru:'||:new.donasi); Body END; / Trigger dijalankan setelah dilakukan contoh perintah berikut : UPDATE keuangan SET donasi=10000000 WHERE nim=”30108001”
Bagian Trigger Trigger timing Trigger event
Keterangan Menunjukkan kapan trigger akan dieksekusi relatif terhadap suatu event. Jenis manipulasi data pada tabel/view yang menyebabkan trigger terpacu.
Trigger restriction (optional) Trigger type
Batasan yang mengizinkan pengeksekusian trigger. Berapa kali body trigger dieksekusi.
Trigger body
Berisi algoritma aksi yang akan dilakukan.
Nilai yang mungkin BEFORE AFER INSTEAD OF INSERT UPDATE DELETE CREATE ALTER DROP STARTUP SHUTDOWN LOGOFF LOGON SERVERERROR SUSPEND … When (boolean value) ROW-level STATEMENT-level Blok PL/SQL
Nama trigger sebaiknya dengan jelas mencerminkan table yang diaplikasikan, Perintah DML Trigger, status before/after dan apakah row level atau statement level. Misalnya trigger BEFORE UPDATE dengan row level pada table KARYAWAN dapat diberi nama bef_upd_row_karyawan. Triggering statement adalah statement/perintah yang menyebabkan trigger tereksekusi.
5. Jenis-jenis Trigger 1. Row-level dan Statement-level Trigger Ketika membuat sebuah trigger, kita dapat menentukan berapa kali aksi trigger di dalamnya dieksekusi: • Row-level trigger dieksekusi untuk setiap row yang dimanipulasi pada suatu transaksi. Dengan kata lain, row-level trigger mengerjakan trigger action satu kali untuk setiap row yang dimanipulasi. Penerapan trigger ini ditunjukkan oleh adanya klausa FOR EACH ROW. Row-level trigger berguna jika kode dalam trigger body bergantung pada setiap baris yang terpengaruh oleh triggering statement. •
Statement-level trigger dieksekusi satu kali pada saat transaksi, tanpa memperhatikan jumlah row yang terlibat. Misalnya, jika terdapat suatu transaksi yang memasukkan 1000 baris ke dalam tabel, maka statement-level trigger hanya akan dieksekusi sekali saja. Statement-level trigger berguna jika kode dalam trigger body tidak bergantung pada baris yang terpengaruh oleh triggering statement. Secara default trigger yang dibuat adalah statement-level trigger.
2. Before and After Trigger Dalam pembuatan sebuah trigger kita dapat menentukan trigger timing yaitu apakah trigger body akan dieksekusi sebelum atau sesudah triggering statement dieksekusi. Before trigger menjalankan trigger body sebelum event atau triggering statement. Oleh karena itu, trigger ini cocok digunakan untuk mendeteksi bagaimana event boleh dilanjutkan maupun tidak. After trigger menjalankan trigger action setelah event terjadi. Kita mungkin akan berhubungan dengan data lama (old) dan data baru (new) yang terjadi dalam transaksi. Dalam trigger, dikenal istilah alias atau referensi, yaitu sejenis variabel yang menyimpan nilai dari suatu kolom dalam tabel. Alias terbagi menjadi dua, yaitu: Untuk mendapatkan data baru pada sebuah tabel di dalam trigger body, dapat dilakukan dengan perintah sebagai berikut: :NEW.nama_kolom
Untuk mendapatkan data lama pada sebuah tabel di dalam trigger body, perintah yang digunakan: :OLD.nama_kolom
Referensi nilai kolom di trigger: • insert (new.nama_kolom) • update (new.nama_kolom dan old.nama_kolom) • delete (old.kolom_name) Before dan After trigger tidak dapat diimplementasikan pada view. 3. Instead Of Trigger Instead of trigger hanya akan dieksekusi bagi view dan diaktivasi jika terjadi perubahan pada base table (tabel asli). Proses yang akan dilakukan oleh triggering statement akan digantikan oleh aksi pada trigger body. 4. System Event & User Event Trigger Penggunaan trigger dapat dikelompokkan menurut event yang terjadi: • System events − Database startup & shutdown − Server error message events − User events
•
User logon and logoff − DDL statements (CREATE, ALTER, and DROP) − DML statements (INSERT, DELETE, and UPDATE)
Contoh 1: Tabel T_HISTORY apabila ada user yang mengubah data pada tabel KEUANGAN. Siapa usernya beserta waktu pengubahan data dan keterangan. Pertama buat dahulu tabel T_HISTORY dengan sintak dibawah ini: SQL> CREATE TABLE T_HISTORY(NAMA_USER VARCHAR2(9), WAKTU VARCHAR2(25), KET VARCHAR2(50)); Table created.
Langkah kedua adalah membuat trigger untuk keperluan di atas sintaksnya sebagai berikut: CREATE OR REPLACE TRIGGER HISTORY_KEUANGAN AFTER INSERT OR DELETE ON keuangan FOR EACH ROW BEGIN INSERT INTO T_HISTORY(NAMA_USER,WAKTU,KET) VALUES (USER, TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'), 'Terjadi perubahan'); DBMS_OUTPUT.PUT('TERJADI PENGUBAHAN PADA TABEL KEUANGAN'); END;
Kemudian lakukan pengujian pada trigger di atas dengan mengisi data pada tabel keuangan dengan sintaks berikut: SQL> INSERT INTO KEUANGAN VALUES ('30108001','10000000','lunas'); TERJADI PENGUBAHAN PADA TABEL KEUANGAN 1 row created.
Untuk melihat tabel T_HISTORY, gunakan sintaks berikut: SQL> SELECT * FROM T_ HISTORY; NAMA_USER PELATIHAN
WAKTU 10-AUG-2003 21:34:45
KET Terjadi perubahan
Contoh 2 : CREATE OR REPLACE TRIGGER BEF_UPD_MAHASISWA BEFORE UPDATE OF NAMA ON MAHASISWA FOR EACH ROW DECLARE VNAMA_BARU VNAMA_LAMA
MAHASISWA.NAMA%TYPE; MAHASISWA.NAMA%TYPE;
BEGIN VNAMA_BARU := :NEW.NAMA; VNAMA_LAMA := :OLD.NAMA; DBMS_OUTPUT.PUT_LINE(‘TELAH DIUPDATE’); DBMS_OUTPUT.PUT_LINE(‘NIM MAHASISWA : ‘||:OLD.NIM); DBMS_OUTPUT.PUT_LINE(‘NAMA MAHASISWA LAMA : ’|| VNAMA_LAMA); DBMS_OUTPUT.PUT_LINE(‘NAMA MAHASISWA BARU : ’|| VNAMA_BARU); END;
Contoh 3 : Misal terdapat tabel STATUS_MHS untuk menyimpan status apakah donasi mahasiswa telah dilunasi CREATE OR REPLACE TRIGGER AFT_INS_KEUANGAN AFTER INSERT ON KEUANGAN FOR EACH ROW WHEN (NEW.donasi > 0) DECLARE BEGIN IF INSERTING THEN UPDATE STATUS_MHS SET STATUS = ‘lunas’ WHERE NIM = :NEW.nim; END IF; END;
6. Cascading Trigger Suatu trigger bisa memicu dijalankannya trigger lain, sehingga terjadi operasi trigger berantai. Dalam cascading operation seperti ini, dapat terjadi kesalahan ORA-04091 atau table is mutating. Hal ini disebabkan karena ada trigger action yang mencoba melakukan operasi query terhadap tabel yang sedang dimodifikasi. Untuk menghindari kesalahan seperti ini, maka trigger harus dimodifikasi agar tidak melakukan pengaksesan tabel yang sedang digunakan. CREATE OR REPLACE TRIGGER aft_del_row_keuangan AFTER DELETE ON keuangan FOR EACH ROW DECLARE n number; BEGIN SELECT COUNT(DISTINCT(nim)) INTO n FROM keuangan; DBMS_OUTPUT.PUT_LINE(‘jumlah mahasiswa yang memberi donasi sebanyak ’|| n); END;
ketika dijalankan perintah berikut SQL> DELETE keuangan WHERE nim = ‘30108001’
Pasti akan muncul kesalahan bahwa tabel keuangan is mutating 7. Status Trigger Untuk mengubah status sebuah trigger dijalankan sintaks berikut Sintaks: ALTER TRIGGER nama_trigger DISABLE | ENABLE;
Keterangan: DISABLE : Untuk menonaktifkan trigger yang sudah dibuat ENABLE : Untuk mengaktifkan kembali trigger yang sudah di DISABLE. Contoh : Untuk men-disable trigger UPDATE_MAHASISWA, gunakan sintaks berikut: ALTER TRIGGER UPDATE_MAHASISWA DISABLE;
Untuk mangaktifkan atau menonaktifkan semua trigger yang diasosiasikan pada sebuah tabel dapat menggunakan sintaks: ALTER TABLE nama_tabel [DISABLE|ENABLE] ALL TRIGGER;
Sedangkan untuk menghapus trigger digunakan perintah berikut : DROP TRIGGER nama_trigger;
Tugas Pendahuluan : 1. Jelaskan perbedaan function, procedure dan package. 2. Jelaskan maksud trigger di bawah ini : CREATE TRIGGER ledger_after_ins_row BEFORE INSERT ON LEDGER_AUDIT FOR EACH ROW BEGIN CALL INSERT_LEDGER_DUP(:NEW.Action_Date,:NEW.Action, :NEW.Item,:NEW.Quantity,:NEW.QuantityType,:NEW.Rate, :NEW.Amount,:new.Person); END;
3. Tambahkan pada database yang telah dibuat pada modul sebelumnya sehingga memenuhi ERD sebagai berikut, kemudian lakukan EXPORT sehingga dapat dimanfaatkan pada pengerjaan praktikum : nama
ID JENIS KATEGORI AG Agama AK Akuntansi BH Bahasa BI Biologi EK Ekonomi FL Filsafat FI Fisika IT Komputer dan Internet MN Manajemen UM Umum
id_supplier
alamat
supplier
no_telp
1 punya id_buku judul pengarang
n buku
harga stock
n punya
1 id_jenis
jenis
kategori
Percobaan : 1. Buatlah sebuah trigger dimana ketika id_jenis pada tabel jenis diubah, maka id_jenis pada tabel buku juga ikut berubah. 2. Buatlah trigger dimana trigger tersebut akan menjamin nilai stok pada tabel buku berkisar antara 0 sampai 100.
