Mata Kuliah : BASIS DATA�Minggu ke 9

Tujuan Perkuliahan

Mahasiswa mampu mendesain SQL (Structured Query Language) .

Materi Pembelajaran

• View.
• Trigger.

• View adalah query tersimpan (stored query) yang ketika dipanggil akan menghasilkan result set.View bertindak seperti tabel virtual.
• Sintaks
• CREATE [OR REPLACE]
• VIEW view_name [(column_list)]
• AS select_statement
• [WITH [CASCADED | LOCAL] CHECK OPTION]
• Pernyataan CREATE VIEW akan membuat view
• Pernyataan REPLACE VIEW akan menindih view yang ada dengan nama yang sama.
• Pernyataan column_list adalah daftar nama kolom yang didefinisikan sendiri.
• Pernyataan select_statement berisi perintah query select DML dari tabel basis atau view yang lain.
• Catatan: VIEW bersifat “frozen”, jika kita membuat view dari query select * from tabel, maka ketika ada kolom baru yang ditambahkan dalam tabel maka dipastikan kolom yang baru tidak akan keluar didalam view.

​

View

• Query untuk menampilkan mahasiswa yang umurnya tua (>22)
• select a.name, a.age from students as a where a.age > 22
• Dapat dibuatkan view untuk menyederhanakan pemanggilan querynya:
• create view mahasiswa_tua as select a.name, a.age from students as a where a.age > 22 with check option
• Untuk mengaksesnya, dapat digunakan perintah query sederhana langsung pada view mahasiswa_tua
• select * from mahasiswa_tua

Contoh : Query 1 Tabel

• Menampilkan nim dan nama mahasiswa, nip dan nama dosen wali
• select a.sid, a.name, b.lid, b.name as nama_dosen from students as a, lecturer as b where a.lid=b.lid
• Dapat dibuatkan view untuk menyederhanakan pemanggilan querynya:
• create view mahasiswa_dosen_wali (nim, nama_mhs, nip, nama_dosen_wali) as select a.sid, a.name, b.lid, b.name as nama_dosen from students as a, lecturer as b where a.lid=b.lid
• Untuk mengaksesnya, dapat digunakan perintah query sederhana langsung pada view mahasiswa_tua
• select * from mahasiswa_dosen_wali

Contoh : Query 2 Tabel

• Untuk menghapus view
• DROP VIEW [IF EXISTS]
• view_name [, view_name] ...
• Yang perlu diketahui tentang view:
• Statement SELECT tidak dapat berisi subquery dalam klausa FROM
• Statement SELECT tidak dapat merujuk pada system atau user vaiables
• Tabel atau view yang dirujuk harus ada
• Tidak dapat merujuk ke tabel temporary, dan tidak boleh membuat view temporary
• Tidak dapat mengasosiasikan view dengan trigger

View

Trigger

• Trigger adalah blok SQL atau prosedur yang berhubungan dengan table, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event.
• Trigger dibuat sesuai dengan keperluan. Trigger perlu dibuat pada saat :
• Membentuk sebuah aksi tertentu terhadap suatu event
• Memusatkan operasi global
• Prosedur bisa dibuat dalam database, kemudian prosedur tersebut dipanggil pada trigger.
• Jika penggunaan trigger terlalu berlebihan, maka akan menyebabkan terjadi sifat ketidaktergantungan yang terlalu kompleks sehingga akan mempersulit pemeliharaan dari aplikasi yang besar.

Ilustrasi Trigger

Komponen Trigger

Sintak penulisan dari database trigger, berisi komponen berikut :

1. Trigger timing :
1. Untuk tabel : BEFORE, AFTER
2. Untuk view : INSTEAD OF 2.
2. Trigger event : INSERT, UPDATE atau DELETE
3. Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger
4. Tipe trigger : Baris atau Pernyataan (statement)
5. klausa WHEN : untuk kondisi pembatasan
6. trigger body : bagian prosedur yang dituliskan pada trigger

Trigger

• Sintak pembuatan:
• CREATE TRIGGER
• trigger_name trigger_time trigger_event
• ON tbl_name FOR EACH ROW trigger_body
• Keterangan:trigger_nama: nama trigger
• trigger_time: nilainya BEFORE atau AFTER aktivitas dilakukan
• trigger_event: statement yang mengaktifkan trigger, nilainya INSERT, UPDATE, atau DELETE
• tbl_name: nama tabel yang akan dibuatkan trigger
• trigger_body: statemen yang dieksekusi ketika trigger diaktifkan. Gunakan BEGIN … END jika ada lebih dari satu statemen.

Trigger

• Kita bisa merujuk kolom dalam tabel subjek (yang diasosiasikan dengan trigger) dengan menggunakan OLD dan NEW
• OLD.nama_kolom merujuk ke kolom dari baris yang lama sebelum di-update atau di-delete.
• NEW.nama_kolom merujuk ke kolom dari baris baru yang akan dimasukkan atau baris yang ada setelah dilakukan update.
• Yang perlu diketahui dalam trigger:
• Action foreign key CASCADE tabel induk tidak mempengaruhi trigger pada tabel anak.
• Tidak boleh ada dua trigger pada satu tabel yang mempunyai action time dan event yang sama. Misal, kita tidak boleh mempunyai dua trigger BEFORE UPDATE dalam satu tabel. Tapi kita boleh membuat trigger BEFORE UPDATE dan BEFORE INSERT dalam satu tabel, atau trigger BEFORE UPDATE dan AFTER update.

Tabel Yang digunakan

Contoh

• Trigger yang diinginkan: Nilai kolom s_count di tabel lecturer akan bertambah satu setiap kali satu mahasiswa dimasukkan dalam tabel sesuai dengan dosen wali yang diikutinya.

delimiter |

CREATE TRIGGER ins_mhs BEFORE INSERT ON students

FOR EACH ROW BEGIN

UPDATE lecturer SET s_count = s_count + 1 where lid = NEW.lid;

END;

|delimiter ;

• Trigger yang diinginkan: Jika mahasiswa dihapus dari tabel students maka jumlah anak wali s_count dosen yang bersangkutan juga akan berkurang satu.

delimiter |

CREATE TRIGGER del_mhs AFTER DELETE ON students

UPDATE lecturer SET s_count = s_count - 1 where lid = OLD.lid;

END;

| delimiter;