Trigger

A.     Definisi Trigger

Trigger adalah blok PL/SQL yang disimpan dalam database dan dijalankan secara implisit sebagai respon terhadap perubahan yang telah ditentukan dalam database.
Perintah DML seperti INSERT, UPDATE, dan DELETE adalah pemicu umum terjadinya trigger. Operasi DDL seperti ALTER dan DROP juga dapat memicu dijalankannya trigger.

B.     Fungsi dan Kelebihan

Fungsi dan kelebihan penggunaan trigger antara lain:
·         Memperbaiki integritas data dengan membuat integrity constraint yang kompleks yang mana tidak mungkin ditangani oleh sintaks pembutan tabel (lihat modul 1).
·         Memvalidasi transaksi data.
·         Memperbaiki keamanan database dengan menyediakan audit yang lebih kompleks mengenai informasi perubahan database dan user siapa yang melakukan perubahan.

C.     Aplikasi yang dapat dilakukan oleh Trigger diantaranya adalah :
·         Membuat isi dari kolom yang diambil dari kolom lain.
·         Membuat mekanisme validasi yang mencakup query pada banyak tabel.
·         Membuat log untuk mendaftarkan penggunaan table.
·         Mengupdate tabel – tabel lain apabila ada penambahan atau perubahan lain di
dalam tabel yang sedang aktif.





SINTAKS

CREATE [OR REPLACE] TRIGGER [user.]nama_trigger
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF nama_kolom [, nama_kolom] ...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}]...
ON [user.]{nama_tabel | nama_view}
[{REFERENCING {OLD [AS] old_value | NEW [AS] new_value} ...]
FOR EACH {ROW | STATEMENT} [WHEN (condition)]
PL/SQL_BLOCK

·         Nama trigger sebaiknya dengan jelas mencerminkan tabel yang diaplikasikan. Perintah DML trigger, status before/after, dan apakah row level atau statement level.

                        misalnya trigger BEFORE UPDATE dengan row level pada tabel KARYAWAN dapat diberi nama bef_upd_row_karyawan.



D.    TIPE TRIGGER
                        a. Row-level and Statement-level Trigger

Row-level and Statement-level trigger merupakan pembagian trigger berdasarkan jumlah aksinya.  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 sedang dimanipulasi. Penerapan trigger ini ditunjukkan oleh adanya klausa FOR EACH ROW.
Statement-level trigger dieksekusi satu kali pada suatu transaksi, tanpa memperhatikan jumlah row yang terlibat. Misalnya, jika terdapat suatu transaksi yang menginsertkan 1000 row ke tabel, maka statement-level trigger hanya akan dieksekusi sekali saja.

                        b. Before and After Trigger

Karena trigger dipicu oleh suatu kejadian (event), maka eksekusinya bisa diatur apakah sebelum atau sesudah event tersebut.
Before trigger menjalankan trigger action sebelum event atau statement pemicu berlangsung. Oleh karena itu, trigger ini cocok digunakan untuk mendeteksi bilamana 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:
:old → variabel yang menyimpan nilai lama kolom sebelum trigger dieksekusi.
:new → variabel yang menyimpan nilai baru kolom setelah trigger dieksekusi.
                         Untuk statement INSERT, alias yang digunakan hanya :new saja, yaitu untuk menyimpan nilai yang akan dimasukkan ke dalam tabel.
                         Untuk UPDATE, alias yang digunakan adalah :new dan :old, sedangkan
                         Untuk DELETE, hanya alias :old saja, yaitu untuk menyimpan nilai yang akan dihapus.

Alias dituliskan di depan nama kolom yang bersangkutan. Penulisannya adalah seperti :new.nama_kolom dan :old.nama_kolom.
                        c. Instead of Trigger

Instead of trigger hanya diperuntukkan bagi view dan diaktivasi jika terjadi perubahan pada base table (tabel asli). Trigger ini tidak dapat menggunakan UPDATE OF nama_kolom maupun BEFORE dan AFTER trigger.

                        Tipe-tipe trigger di atas dapat dikombinasikan menjadi 14 macam :
                         
                        1. BEFORE INSERT row
                        2. BEFORE INSERT statement
                        3. AFTER INSERT row
                        4. AFTER INSERT statement
                        5. BEFORE UPDATE row
                        6. BEFORE UPDATE statement
                        7. AFTER UPDATE row
                        8. AFTER UPDATE statement
                        9. BEFORE DELETE row
                        10. BEFORE DELETE statement
                        11. AFTER DELETE row
                        12. AFTER DELETE statement
                        13. INSTEAD OF row
                        14. INSTEAD OF statement
                         
E.     BATASAN TRIGGER
Dalam penggunaannya, trigger memiliki batasan sebagai berikut:
                         Tidak dapat menggunakan perintah ROLLBACK dan COMMIT.
                         Tidak dapat memanggil fungsi dan prosedur yang memiliki ROLLBACK dan COMMIT.
                         Tidak dapat diimplementasikan pada kolom suatu tabel yang memiliki constraint, jika pada akhirnya akan menyebabkan pelanggaran constraint.

Contoh STUDI KASUS

Sebuah apotek mempunyai database yang di dalamnya terdapat tabel-tabel dengan spesifikasi antara lain sebagai berikut:

F.     MENGUBAH STATUS TRIGGER

Pada saat diciptakan, trigger berstatus aktif (enable). Kita dapat mengubah status trigger dengan perintah-perintah berikut:

                         ALTER TRIGGER nama_trigger DISABLE

Sintaks tersebut digunakan untuk menonaktifkan trigger yang dibuat.
                         ALTER TABLE nama_tabel DISABLE ALL TRIGGER

Syntax tersebut digunakan untuk menonaktifkan semua trigger yang ada pada suatu tabel.
                         ALTER TRIGGER nama_trigger ENABLE
                         ALTER TABLE nama_tabel ENABLE ALL TRIGGER
                         DROP TRIGGER nama_trigger

Syntax tersebut digunakan untuk menghapus trigger yang telah dibuat.

Menonaktifkan Trigger
Sintaks:

Keterangan:
DISABLE : untuk menonaktifkan trigger yang sudah dibuat
ENABLE : untuk mengaktifkan kembali trigger yang sudah di DISABLE.
Contoh 10.4 :
Untuk men-disable trigger UPDATE_PEGAWAI, gunakan sintaks berikut:



Contoh Trigger :

Pernyataan CREATE TRIGGER digunakan untuk membuat trigger, termasuk
aksi apa yang dilakukan saat trigger diaktifkan. Trigger berisi program yang
dihubungkan dengan suatu tabel atau view yang secara otomatis melakukan suatu aksi
ketika suatu baris di dalam tabel atau view dikenai operasi INSERT, UPDATE atau
DELETE.

Sintak :
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

Keterangan :
·         [DEFINER = { user | CURRENT_USER }]: Definisi user yang sedang aktif,
sifatnya opsional.
·         trigger_name: Nama trigger.
·         trigger_time: waktu menjalankan trigger. Ini dapat berupa BEFORE atau AFTER.
ü  BEFORE: Membuat trigger diaktifkan sebelum dihubungkan dengan suatu
operasi.
ü  AFTER: Membuat trigger diaktifkan setelah dihubungkan dengan suatu
operasi.
·         trigger_event: berupa kejadian yang akan dijalankan trigger.
·         trigger_event dapat berupa salah satu dari berikut:

ü  INSERT : trigger diaktifkan ketika sebuah record baru disisipkan ke dalam tabel. Contoh: statemen INSERT, LOAD DATA, dan REPLACE.
ü  UPDATE : trigger diaktifkan ketika sebuah record dimodifikasi. Contoh: statemen UPDATE.
ü  DELETE : trigger diaktifkan ketika sebuah record dihapus. Contoh: statemen DELETE dan REPLACE.
Catatan : trigger_event tidak merepresentasikan statemen SQL yang
diaktifkan trigger sebagai suatu operasi tabel. Sebagai contoh, trigger BEFORE
untuk INSERT akan diaktifkan tidak hanya oleh statemen INSERT tetapi juga
statemen LOAD DATA.

·         tbl_name: Nama tabel yang berasosiasi dengan trigger.
·         trigger_stmt: Statemen (tunggal atau jamak) yang akan dijalankan ketika trigger
aktif.
Contoh yang akan dibahas adalah mencatat kejadian-kejadian yang terjadi
beserta waktunya pada tabel mahasiswa, dan catatan-catatan tadi disimpan dalam tabel
yang lain, misal log_mhs. Misalkan struktur tabel log_mhs adalah sebagai berikut.

Contoh 1:
mysql> create trigger ins_mhs after insert on mahasiswa
-> for each row insert into log_mhs values('Tambah data',now());
mysql> insert into mahasiswa values('00631','Hanif','Kalasan','P01');
mysql> select * from log_mhs;

Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa
disisipkan (insert), maka secara otomatis tabel log_mhs akan disisipkan satu record,
yaitu kejadian ‘Tambah data’ dan waktu saat record pada tabel mahasiswa disisipkan.
Contoh 2 :
mysql> create trigger updt_mhs after update on mahasiswa
-> for each row insert into log_mhs values('Ubah data',now());
mysql> update mahasiswa set nama='Moh. Riyan' where nim='00543';


Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa
diperbaharui (update), maka secara otomatis tabel log_mhs akan disisipkan satu record,
yaitu kejadian ‘Ubah data’ dan waktu saat record pada tabel mahasiswa
diperbaharui.

Contoh 3 :
mysql> create trigger del_mhs after delete on mahasiswa
-> for each row insert into log_mhs values('Hapus data',now());
mysql> delete from mahasiswa where nim='00631';

Dari contoh diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa
dihapus (delete), maka secara otomatis tabel log_mhs akan disisipkan satu record, yaitu
kejadian ‘Hapus data’ dan waktu saat record pada tabel mahasiswa dihapus.
Dalam implementasinya untuk pekerjaan sehari-hari, pembuatan trigger dan
tabel log, digunakan untuk mencatat kejadian suatu tabel yang dianggap rawan serangan
cracker. Dengan struktur trigger yang baik sesuai kebutuhan, administrator dapat
melakukan pelacakan dan recovery data dengan cepat karena mengetahui record mana
saja yang “diserang”. Atau, dihubungkan dengan program aplikasi (user interface) agar
mengaktifkan alarm, jika terdapat operasi database pada waktu yang tidak seharusnya
(misalkan malam hari).

Ø  Menampilkan daftar trigger yang telah dibuat:


Keterangan (record pada kolom “statement” dan “sql_mode”):
S1 : insert into log_mhs values('Tambah data',now())
S2 : insert into log_mhs values('Ubah data',now())
S3 : insert into log_mhs values('Hapus data',now())
SQL1 : STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL2 : STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL3 : STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION









CONTOH :
1.     Row-Level Trigger: Contoh-1
v  Tables:
§  Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
§  Department (Dname, DNO, Total_Sal, Manager_SSN)
            CREATE TRIGGER TotalSal_1
                        AFTER INSERT ON Employee
            REFERENCING OLD ROW AS O, NEW ROW AS N
            FOR EACH ROW
WHEN (N.DNO IS NOT NULL)
            UPDATE Department
            SET Total_Sal = Total_Sal + N.Salary
            WHERE DNO = N.DNO;

2.     Row-Level Trigger: Contoh-2
v  Tables:
§  Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
§  Department (Dname, DNO, Total_Sal, Manager_SSN)
            CREATE TRIGGER TotalSal_2
                        AFTER UPDATE OF DNO ON Employee
            REFERENCING OLD ROW AS O, NEW ROW AS N
            FOR EACH ROW
BEGIN
                        UPDATE Department
            SET Total_sal = Total_Sal + N.Salary
            WHERE DNO = N.DNO;
                        UPDATE Department
            SET Total_sal = Total_Sal - O.Salary
            WHERE DNO = O.DNO
            END;


3.     Row-Level Trigger: Contoh-3
v  Tables:
§  Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
§  Department (Dname, DNO, Total_Sal, Manager_SSN)
            CREATE TRIGGER TotalSal_3
                        AFTER DELETE ON Employee
            REFERENCING OLD ROW AS O
            FOR EACH ROW
WHEN (O.DNO IS NOT NULL)
                        UPDATE Department
            SET Total_sal = Total_Sal - O.Salary
            WHERE DNO = O.DNO
            END;

4.     Row-Level Trigger: Contoh-4
v  Tables:
§  Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
§  Department (Dname, DNO, Total_Sal, Manager_SSN)
            CREATE TRIGGER Inform_Supervisor_1
                        BEFORE INSERT OR UPDATE OF Salary, Supervisor_SSN ON EMPLOYEE
            REFERENCING OLD ROW AS O, NEW ROW AS N
            FOR EACH ROW
WHEN
                        (N.Salary > ( SELECT Salary FROM Employee
                                                   WHERE SSN = N.Supervisor_SSN
                                                )
            )
            inform_supervisor (N.Supervisor_SNN, N.SSN);
            //Diasumsikan “inform_supervisor” sebagai sebuah external procedure untuk
     memberikan notifikasi kepada supervisor

5.     Statement-Level Trigger: Contoh-1
v  Tables:
§  Employee (Name, SSN, Salary, Dno, Supervisor_SSN)
§  Department (Dname, DNO, Total_Sal, Manager_SSN)
            CREATE TRIGGER TotalSal_4
                                    AFTER UPDATE OF Salary ON EMPLOYEE
            REFERENCING OLD TABLE AS O, NEW TABLE AS N
            FOR EACH STATEMENT
WHEN
                                    EXISTS ( SELECT * FROM N WHERE N.DNO IS NOT NULL ) OR
                        EXISTS ( SELECT * FROM O WHERE O.DNO IS NOT NULL )
            UPDATE Department AS D
            SET D.Total_Sal =D.Total_Sal           
                                + ( SELECT SUM (N.Salary) FROM N WHERE D.DNO = N.DNO )
                                                      -  ( SELECT SUM (O.Salary) FROM O WHERE D.DNO = O.DNO )
            WHERE DNO IN ( (SELECT DNO FROM N)
                                      UNION
                                      (SELECT DNO FROM O)
                                    );

6.     Statement-Level Trigger: Contoh-2
v  Table:
§  Sailors (sid, name, age, rating)
CREATE TRIGGER youngSailorUpdate
                        AFTER INSERT ON Sailors
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
                 INSERT
                            INTO YoungSailors(sid, name, age, rating)
                            SELECT sid, name, age, rating
                            FROM NewSailors N
                            WHERE N.age <= 18

7.     Statement-Level Trigger: Contoh-3
v  Table:
§  Students (sid, name, age, gpa)
CREATE TRIGGER setCount
                        AFTER INSERT ON Students
REFERENCING NEW TABLE AS InsertedTuples
FOR EACH STATEMENT
                 INSERT
                            INTO StatisticsTable(ModifiedTable,      
                                                  ModificationType, Count)
                            SELECT ‘Students’, ‘Insert’, COUNT *
                            FROM InsertedTuples I
                            WHERE I.age < 18
Read More.. >>