Praktikum 5
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;
|
Bagian Trigger
|
Keterangan
|
Nilai
yang mungkin
|
Trigger timing
|
Menunjukkan kapan
trigger akan dieksekusi relatif
terhadap suatu event.
|
BEFORE AFER
INSTEAD OF
|
Trigger event
|
Jenis manipulasi data pada tabel/view yang menyebabkan trigger terpacu.
|
INSERT UPDATE
DELETE CREATE
ALTER DROP STARTUP
SHUTDOWN
LOGOFF
LOGON
SERVERERROR
SUSPEND
…
|
Trigger restriction
(optional)
|
Batasan yang mengizinkan
pengeksekusian trigger.
|
When
(boolean value)
|
Trigger type
|
Berapa
kali body trigger dieksekusi.
|
ROW-level
STATEMENT-level
|
Trigger body
|
Berisi algoritma aksi yang
akan dilakukan.
|
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 WAKTU KET
PELATIHAN
10-AUG-2003 21:34:45 Terjadi perubahan
Contoh 2 :
CREATE OR REPLACE
TRIGGER BEF_UPD_MAHASISWA BEFORE UPDATE OF NAMA
ON MAHASISWA
FOR EACH ROW
DECLARE
VNAMA_BARU MAHASISWA.NAMA%TYPE;
VNAMA_LAMA 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;
|
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
|
3. Tambahkan
pada database yang telah dibuat pada modul sebelumnya sehingga memenuhi ERD
sebagai berikut, kemudian lakukan EXPORT sehingga dapat dimanfaatkan pada
pengerjaan praktikum :
Percobaan :
1.
Lakukan IMPORT
atas database yang telah dibuat.
2.
Buatlah sebuah trigger dimana ketika id_jenis pada
tabel jenis diubah, maka id_jenis pada tabel buku juga ikut berubah.
3.
Buatlah trigger dimana trigger tersebut akan menjamin
nilai stok pada tabel buku berkisar antara 0 sampai 100.
jawaban nya mana
ReplyDelete