Materi Praktikum Minggu Ke-4 SMBD 2013/2014

MATERI PART 4 – Prak. BASISDATA

VIEW dan TRIGGER

LABORATORIUM 4 – BASISDATA

MENU PART INI :

1. View
1. Algorithm Attributes
2. Updatable View
3. Klausa WITH CHECK OPTION
4. Merubah View
5. Melihat definisi pembuatan view

2. Trigger

Membuat trigger
BEFORE TRIGGER
AFTER TRIGGER
Melihat trigger yang sudah dibuat

VIEW

View adalah query tersimpan yang menghasilkan result set ketika dipanggil. View bertindak sebagai tabel virtual. Beberapa hal yang tidak boleh digunakan pada query yang mendefinisikan view adalah sebagai berikut:

• definisi view tidak boleh memiliki sub query di klausa FROM dari statement SQL
• variabel user, sistem, atau lokal tidak boleh digunakan dalam sebuah SQL SELECT
• view tidak dapat merujuk ke tabel temporer
• trigger tidak dapat diasosiasikan terhadap sebuah view
• view yang dibuat di dalam stored procedure tidak dapat merujuk kepada parameter dalam stored procedure

Pembuatan view memerlukan pendefinisian nama view dan sebuah statement SQL. Setelah view dibuat, view dapat diquery seperti tabel biasa. Bentuk dasar pembuatan view adalah sebagai berikut:

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name [WHERE condition]

Pembuatan view dapat menggunakan OR REPLACE untuk mengganti view yang telah ada sebelumnya.
Berikut adalah contoh pembuatan view sederhana.

mysql> CREATE VIEW view_1 AS SELECT * FROM pegawai;
Query OK, 0 rows affected (0.06 sec)

Setelah view dibuat maka untuk melihat hasilnya kita harus memanggilnya dengan perintah select, contohnya :

mysql> select*from view_1;
+———-+———————–+————-+—————-+———+
| nip      | nama_peg              | alamat_peg | jabatan        | gaji    |
+———-+———————–+————-+—————-+———+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O          | 9000000 |
| PEG-1002 | Felix Nababan         | Medan       | Manager        | 8000000 |
| PEG-1003 | Olga Syahputra        | Jakarta     | Kepala Unit    | 6000000 |
| PEG-1004 | Chelsea Olivia        | Bandung     | Kepala Unit    | 6000000 |
| PEG-1005 | Tuti Wardani          | Jawa Tengah | Supervisor     | 4500000 |
| PEG-1006 | Budi Drajat           | Malang      | Supervisor     | 4500000 |
| PEG-1007 | Bambang Pamungkas     | Kudus       | Staff Senior   | 3000000 |
| PEG-1008 | Ely Oktafiani         | Yogyakarta | Staff Senior   | 3000000 |
| PEG-1009 | Rani Wijaya           | Magelang    | Staff Senior   | 3000000 |
| PEG-1010 | Rano Karno            | Solo       | Staff Junior   | 2000000 |
| PEG-1011 | Rahmadi Sholeh        | Yogyakarta | Staff Junior   | 2000000 |
| PEG-1012 | Ilham Ungara          | Jakarta     | Staff Junior   | 2000000 |
| PEG-1013 | Endang Melati         | Madiun      | Staff Junior   | 2000000 |
| PEG-1014 | Donny Damara          | Makasar     | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem                | Yogyakarta | Tenaga Kontrak | 500000 |
+———-+———————–+————-+—————-+———+
15 rows in set (0.00 sec)

Dalam peamanggilannya pun kita juga bisa menentukan kondisi yang kita inginkan, contohnya :

mysql> select*from view_1 limit 3;
+———-+———————–+————+————-+———+
| nip      | nama_peg              | alamat_peg | jabatan     | gaji    |
+———-+———————–+————+————-+———+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O       | 9000000 |
| PEG-1002 | Felix Nababan         | Medan      | Manager     | 8000000 |
| PEG-1003 | Olga Syahputra        | Jakarta    | Kepala Unit | 6000000 |
+———-+———————–+————+————-+———+
3 rows in set (0.00 sec)
mysql> select*from view_1 where nama_peg = ‘Olga Syahputra’;
+———-+—————-+————+————-+———+
| nip      | nama_peg       | alamat_peg | jabatan     | gaji    |
+———-+—————-+————+————-+———+
| PEG-1003 | Olga Syahputra | Jakarta    | Kepala Unit | 6000000 |
+———-+—————-+————+————-+———+
1 row in set (0.00 sec)

Penggunaan WHERE dalam syntax VIEW

mysql>
CREATE VIEW view_2
AS SELECT nip, nama_peg, gaji
FROM pegawai
WHERE alamat_peg=’Yogyakarta’;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from view_2;
+———-+———————–+———+
| nip      | nama_peg              | gaji    |
+———-+———————–+———+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1008 | Ely Oktafiani         | 3000000 |
| PEG-1011 | Rahmadi Sholeh        | 2000000 |
| PEG-1015 | Paijem                | 500000 |
+———-+———————–+———+
4 rows in set (0.00 sec)

1. Algorithm Attributes
Algorithm attributes memberikan kendali bagaimana MySQL memproses view. Atribut ini bersifat opsional. Atribut algoritma menerima tiga buah nilai, yaitu MERGE, TEMPTABLE, dan UNDEFINED.
Default algoritma yang dipilih adalah UNDEFINED.

Untuk algoritma MERGE, teks dari statement query yang merujuk ke sebuah view dan definisi view digabung sedemikian sehingga bagian dari definisi view menggantikan bagian yang bersesuaian dengan statement.

Untuk algoritma TEMPTABLE, hasil dari view diambil ke dalam temporary table, yang kemudian digunakan untuk menjalankan statement. TEMPTABLE dipilih karena lock pada table yang digunakan dapat langsung dilepas setelah temporary table telah dibuat. Akibatnya, penggunaan TEMPTABLE dapat mempercepat pelepasan lock pada table utama, sehingga klien lain yang akan menggunakan view tidak menunggu terlalu lama.

Untuk algoritma UNDEFINED, MySQL memilih sendiri algoritma mana yang akan digunakan. MySQL lebih memilih MERGE karena biasanya lebih efisien. Selain itu, view yang menggunakan TEMPTABLE tidak dapat diupdate karena menggunakan temporary table.

Berikut adalah contoh pembuatan view menggunakan atribut algoritma MERGE.

mysql> CREATE ALGORITHM = MERGE VIEW v_merge (nomor_induk_pegawai, nama_pegawai,
gaji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000;

Query OK, 0 rows affected (0.03 sec)

mysql> select*from v_merge;
+———————+———————–+———+
| nomor_induk_pegawai | nama_pegawai          | gaji    |
+———————+———————–+———+
| PEG-1001            | Soeharto Mangundirejo | 9000000 |
| PEG-1002            | Felix Nababan         | 8000000 |
| PEG-1003            | Olga Syahputra        | 6000000 |
| PEG-1004            | Chelsea Olivia        | 6000000 |
| PEG-1005            | Tuti Wardani          | 4500000 |
| PEG-1006            | Budi Drajat           | 4500000 |
| PEG-1007            | Bambang Pamungkas     | 3000000 |
| PEG-1008            | Ely Oktafiani         | 3000000 |
| PEG-1009            | Rani Wijaya           | 3000000 |
+———————+———————–+———+
9 rows in set (0.00 sec)

Adanya tambahan syntax ALGORITHM=MERGE tersebut bertujuan untuk mendeklarasikan Algoritma mana yang kita pilih, jika kita tidak mendeklarasikannya punjuga bisa karena MySQL bisa memilih sendiri contoh :

mysql> CREATE VIEW v_merge2(nomor_induk_pegawai, nama_pegawai, gaji) AS SELECT n
ip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000;
Query OK, 0 rows affected (0.03 sec)

mysql> select*from v_merge2;
+———————+———————–+———+
| nomor_induk_pegawai | nama_pegawai          | gaji    |
+———————+———————–+———+
| PEG-1001            | Soeharto Mangundirejo | 9000000 |
| PEG-1002            | Felix Nababan         | 8000000 |
| PEG-1003            | Olga Syahputra        | 6000000 |
| PEG-1004            | Chelsea Olivia        | 6000000 |
| PEG-1005            | Tuti Wardani          | 4500000 |
| PEG-1006            | Budi Drajat           | 4500000 |
| PEG-1007            | Bambang Pamungkas     | 3000000 |
| PEG-1008            | Ely Oktafiani         | 3000000 |
| PEG-1009            | Rani Wijaya           | 3000000 |
+———————+———————–+———+
9 rows in set (0.00 sec)
Algoritma MERGE tidak dapat digunakan ketika view memerlukan penggunaan konstruksi sebagai berikut:

• Fungsi aggregate, seperti SUM(), MIN(), MAX(), COUNT() dan lain-lain
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION atau UNION ALL
• Subquery pada SELECT
• Referensi ke nilai literal (tidak ada tabel yang direferensi)

Ketika algoritma MERGE tidak dapat digunakan, kita bisa menggunakan algoritma TEMPTABLE.

mysql> CREATE ALGORITHM = MERGE VIEW view_4(nomor_induk_pegawai, nama_pegawai, g
aji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000 LIMIT 5;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+———+——+——————————————————————————-+
| Level   | Code | Message                                                               |
+———+——+——————————————————————————-+
| Warning | 1354 | View merge algorithm can’t be used here for now (assumed undefined algorithm) |
+———+——+——————————————————————————-+
1 row in set (0.00 sec)

mysql> CREATE ALGORITHM = TEMPTABLE VIEW v_temptable(nomor_induk_pegawai, nama_p
egawai, gaji) AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji > 2000000 LI
MIT 5;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_temptable;
+———————+———————–+———+
| nomor_induk_pegawai | nama_pegawai          | gaji    |
+———————+———————–+———+
| PEG-1001            | Soeharto Mangundirejo | 9000000 |
| PEG-1002            | Felix Nababan         | 8000000 |
| PEG-1003            | Olga Syahputra        | 6000000 |
| PEG-1004            | Chelsea Olivia        | 6000000 |
| PEG-1005            | Tuti Wardani          | 4500000 |
+———————+———————–+———+
5 rows in set (0.00 sec)
Ketika kita mendefinisikan VIEW menggunakan algoritma TEMPTABLE tidak muncul pesan warning.

2. Updateable VIEW
Ada beberapa view yang dapat diupdate menggunakan statement UPDATE, DELETE, atau INSERT.
Update juga dilakukan pada tabel yang dirujuk view tersebut. Supaya sebuah view dapat diupdate, harus ada hubungan satu-satu antara baris dalam view dengan baris pada tabelnya. Selain view yang dapat diupdate ada juga view yang tidak dapat diupdate, yaitu view yang memiliki:

• fungsi aggregat, seperti SUM(), MIN(), MAX(), COUNT().
• DISTINCT
• GROUP BY
• HAVING
• sub query di SELECT
• Join-join tertentu
• Nonupdatable view di klausa FROM
• Sebuah subquery di klausa WHERE yang merujuk ke sebuah tabel dalam klausa FROM.
• Hanya merujuk ke nilai literal (tidak merujuk ke sebuah tabel)
• Menggunakan ALGORITHM = TEMPTABLE (penggunaan temporary table membuat view tidak dapat diupdate)
• Referensi berulangkali ke kolom manapun dari sebuah tabel.

    mysql> UPDATE v_merge2 SET nama_pegawai = ‘Olga Syahrini’ WHERE nama_pegawai = ‘
Olga Syahputra’;

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Lalu kita lihat perubahannya pada hasil eksekusi v_merge2;

mysql> select * from v_merge2;
+———————+———————–+———+
| nomor_induk_pegawai | nama_pegawai          | gaji    |
+———————+———————–+———+
| PEG-1001            | Soeharto Mangundirejo | 9000000 |
| PEG-1002            | Felix Nababan         | 8000000 |
| PEG-1003            | Olga Syahrini         | 6000000 |
| PEG-1004            | Chelsea Olivia        | 6000000 |
| PEG-1005            | Tuti Wardani          | 4500000 |
| PEG-1006            | Budi Drajat           | 4500000 |
| PEG-1007            | Bambang Pamungkas     | 3000000 |
| PEG-1008            | Ely Oktafiani         | 3000000 |
| PEG-1009            | Rani Wijaya           | 3000000 |
+———————+———————–+———+
9 rows in set (0.00 sec)

Update pada view v_merge2 juga berefek pada tabel sebenarnya, sehingga update pada view bukan hanya merubah data dlm view tetapi merubah data secara keseluruhan.
Kita bisa lihat sbb :

mysql> select * from pegawai;
+———-+———————–+————-+—————-+———+
| nip      | nama_peg              | alamat_peg | jabatan        | gaji    |
+———-+———————–+————-+—————-+———+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O          | 9000000 |
| PEG-1002 | Felix Nababan         | Medan       | Manager        | 8000000 |
| PEG-1003 | Olga Syahrini         | Jakarta     | Kepala Unit    | 6000000 |
| PEG-1004 | Chelsea Olivia        | Bandung     | Kepala Unit    | 6000000 |
| PEG-1005 | Tuti Wardani          | Jawa Tengah | Supervisor     | 4500000 |
| PEG-1006 | Budi Drajat           | Malang      | Supervisor     | 4500000 |
| PEG-1007 | Bambang Pamungkas     | Kudus       | Staff Senior   | 3000000 |
| PEG-1008 | Ely Oktafiani         | Yogyakarta | Staff Senior   | 3000000 |
| PEG-1009 | Rani Wijaya           | Magelang    | Staff Senior   | 3000000 |
| PEG-1010 | Rano Karno            | Solo        | Staff Junior   | 2000000 |
| PEG-1011 | Rahmadi Sholeh        | Yogyakarta | Staff Junior   | 2000000 |
| PEG-1012 | Ilham Ungara          | Jakarta     | Staff Junior   | 2000000 |
| PEG-1013 | Endang Melati         | Madiun      | Staff Junior   | 2000000 |
| PEG-1014 | Donny Damara          | Makasar     | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem                | Yogyakarta | Tenaga Kontrak | 500000 |
+———-+———————–+————-+—————-+———+
15 rows in set (0.00 sec)

Sekarang kita coba mengupdate view yang memuat “larangan” tadi, contoh kita akan mengupdate v_temptable yang merupakan temptable view, maka syntaxnya :

mysql> UPDATE v_temptable SET nama_pegawai = ‘Chelsea Burik’ WHERE nama_pegawai
= ‘Chelsea Olivia’;
ERROR 1288 (HY000): The target table v_temptable of the UPDATE is not updatable

3. Klausa WITH CHECK OPTION
Klausa WITH CHECK OPTION dalam pembuatan view diberikan kepada updatable view untuk mencegah INSERT atau UPDATE pada baris data kecuali klausa WHERE pada statement SELECT bernilai benar (true). Parameter yang dapat digunakan untuk CHECK OPTION adalah LOCAL dan CASCADED. Parameter LOCAL berarti pengecekan hanya dilakukan pada view yang didefinisikan. Parameter CASCADED berarti pengecekan dilakukan pada view beserta tabel yang direferensi oleh view tersebut. Secara default, pendefinisian CHECK OPTION bernilai CASCADED.

Berikut adalah contoh definisi view menggunakan CHECK OPTION:

mysql> CREATE VIEW v_1 AS SELECT nip, nama_peg, gaji FROM pegawai WHERE gaji < 2
000000 WITH CHECK OPTION;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_1;
+———-+—————+———+
| nip      | nama_peg      | gaji    |
+———-+—————+———+
| PEG-1014 | Donny Damara | 1000000 |
| PEG-1015 | Paijem        | 500000 |
| PEG-1016 | Djono         | 1000000 |
+———-+—————+———+
3 rows in set (0.00 sec)

mysql> CREATE VIEW v_2 AS SELECT * FROM v_1 WHERE gaji > 500000 WITH LOCAL CHECK
OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_2;
+———-+—————+———+
| nip      | nama_peg      | gaji    |
+———-+—————+———+
| PEG-1014 | Donny Damara | 1000000 |
| PEG-1016 | Djono         | 1000000 |
+———-+—————+———+
2 rows in set (0.00 sec)

mysql> CREATE VIEW v_3 AS SELECT * FROM v_1 WHERE gaji > 500000 WITH CASCADED CH ECK OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_3;
+———-+—————+———+
| nip      | nama_peg      | gaji    |
+———-+—————+———+
| PEG-1014 | Donny Damara | 1000000 |
| PEG-1016 | Djono         | 1000000 |
+———-+—————+———+
2 rows in set (0.00 sec)

Pada View v_1 kita membuat sebuah view untuk menampilkan data pegawai yang memiliki gaji kurang dari 2jt, lalu kita membuat view v_2 yang berfungsi untuk mengambil data dari hasil eksekusi view v_1 tetapi dibatasi data pegawai yang memiliki gaji lebih dari 500rb dan juga memuat klausa WITH LOCAL CHECK OPTION, sedangkan view_v3 fungsinya sama dengan view v_2 tetapi bedanya view v_3 menggunakan klausa WITH CASCADED CHECK OPTION. Untuk melihat perbedaannya kita bisa melakukan masukkan data sbb :

mysql> insert into v_2 values(‘PEG-1018′,’Robert Gugu’,3000000);
Query OK, 1 row affected (0.05 sec)

mysql> insert into v_3 values(‘PEG-1018′,’Robert Gugu’,3000000);
ERROR 1369 (HY000): CHECK OPTION failed ‘lab4.v_3′

Insert data pada view v_2 dinyatakan tak ada masalah karena v_2 hanya melihat atau mengechek secara LOCAL atau hanya yang data lebih dari 500rb, sedangkan untuk v_3 dinyatakan error karena dia mengecheck secara CASCADED atau keseluruhan, mulai dari pengecheckan v_1 yang direferensikan oleh v_3, dan karena v_1 memiliki batasan 2jt maka masukkan 3jt ke v_3 dianggap salah.

4. Merubah VIEW
View yang telah kita buat bisa kita modifikasi dengan ALTER VIEW, contohnya sbb :

mysql> ALTER VIEW view_2(NIP, NAMA_PEGAWAI, GAJI) AS SELECT nip, nama_peg, gaji
FROM pegawai WHERE alamat_peg=’Yogyakarta’;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from view_2;
+———-+———————–+———+
| NIP      | NAMA_PEGAWAI          | GAJI    |
+———-+———————–+———+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1008 | Ely Oktafiani         | 3000000 |
| PEG-1011 | Rahmadi Sholeh        | 2000000 |
| PEG-1015 | Paijem                | 500000 |
| PEG-1016 | Djono                 | 1000000 |
+———-+———————–+———+
5 rows in set (0.02 sec)

5. Melihat Definisi Pembuatan VIEW
Kita bisa melihat kembali syntax kita dalam membuat view, kita bisa menggunakan syntax :
SHOW CREATE VIEW<nama_view>
mysql> show create view view_2\G;

atau jika kita ingin melihat struktur viewnya kita juga bisa gunakan perintah :

DESC <nama_view>

mysql> desc view_2;
+————–+————-+——+—–+———+——-+
| Field        | Type        | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| NIP          | varchar(8) | NO   |     | NULL    |       |
| NAMA_PEGAWAI | varchar(50) | YES |     | NULL    |       |
| GAJI         | int(7)      | YES |     | NULL    |       |
+————–+————-+——+—–+———+——-+
3 rows in set (0.02 sec)

TRIGGER

Trigger adalah sebuah objek database yang diasosiasikan dengan sebuah tabel. Trigger diaktifkan ketika sebuah event terjadi pada tabel yang diasosiasikan. Tabel yang diasosiasikan dengan trigger haruslah sebuah tabel yang permanen dan bukan temporary tabel.

1. Membuat Trigger
Trigger bisa dibuat dengan syntax sbb :

CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event>
ON <table>
FOR EACH ROW <trigger_body statements>

Pada sintaks di atas, trigger_name adalah nama dari trigger yang akan kita buat.
Trigger_time adalah saat aktivasi trigger. Parameter trigger_time dapat berisi BEFORE atau AFTER, yang menandakan apakah aktivasi trigger dilakukan sebelum atau sesudah terjadi perubahan pada tabel.
Trigger_event menunjukkan jenis statement yang mengaktifkan trigger.

Trigger_event dapat didefinisikan sebagai salah satu dari:

INSERT: trigger diaktifkan ketika sebuah baris baru ditambahkan ke tabel, sebagai contoh melalui statement INSERT, LOAD DATA, atau REPLACE.
UPDATE: trigger diaktifkan ketika sebuah baris dimodifikasi, sebagai contoh melalui statement UPDATE.
DELETE: trigger diaktifkan ketika sebuah baris dihapus, melalui statement DELETE dan REPLACE. Namun demikian, DROP TABLE dan TRUNCATE TABLE tidak mengaktifkan trigger DELETE

Blog Bebas

Selasa, 21 Januari 2014

MATERI PART 4 – Prak. BASISDATA

Materi Praktikum Minggu Ke-4 SMBD 2013/2014

Tidak ada komentar:

Posting Komentar