Basis Data II. Pertemuan Ke-9 dan ke-10 (Stored Procedure dan Trigger) Noor Ifada S1 T. Informatika - UTM (2012) 1

Basis Data II Pertemuan Ke-9 dan ke-10 (Stored Procedure dan Trigger) Noor Ifada

[email protected] S1 T. Informatika - UTM (2012)

1

Sub Pokok Bahasan Stored Procedure vs Trigger Stored Procedure Trigger

S1 T. Informatika - UTM (2012)

2

Stored Procedure vs Trigger  Persamaan: – Obyeknya dapat berupa TABEL, VIEW, dll – Tersimpan di dalam System Catalog basisdata – Terdiri dari penyataan SQL yang deklaratif (seperti CREATE, UPDATE, dan SELECT) dan prosedural (seperti IF-THEN-ELSE dan WHILE-DO)  Perbedaan: – Stored procedure diaktifkan sebagai suatu pernyataan oleh SQL editor, program, atau oleh stored procedure atau trigger lain – Trigger diaktifkan hanya oleh RDBMS dalam suatu kondisi tertentu (ketika pernyataan INSERT, UPDATE, DELETE dieksekusi) S1 T. Informatika - UTM (2012)

3

STORED PROCEDURE Create Stored Procedure:  
procedure statement> ::= CREATE PROCEDURE <procedure name> ( [ <parameter list> ] ) <parameter list> ::= <parameter specification> [ , <parameter specification> ]... <parameter specification> ::= [ IN | OUT | INOUT ] <parameter> ::= ::= [ : ] BEGIN <statement list> END [ ] <statement list> ::= { ; }... <statement in body> ::= <declarative statement> | <procedural statement>

Aktivasi/pemanggilan Stored Procedure:  
statement> ::= CALL [ . ] <stored procedure name> ( [ <scalar expression> [ , <scalar expression> ]... ] )

Menghapus Stored Procedure: ::= DROP PROCEDURE [ IF EXISTS ] [ . ] <procedure name> S1 T. Informatika - UTM (2012)

4

Contoh Stored Procedure Contoh 1: Buatlah stored procedure untuk menghapus seluruh pertandingan yang pernah dimainkan oleh suatu pemain tertentu!  

CREATE PROCEDURE DELETE_MATCHES (IN P_PLAYERNO INTEGER) BEGIN DELETE FROM MATCHES WHERE PLAYERNO = P_PLAYERNO; END

Contoh 2: Hapuslah seluruh pertandingan yang pernah dimainkan oleh pemain nomor 8 dengan penggunakan prosedur DELETE_MATCHES!  

CALL DELETE_MATCHES (8)

S1 T. Informatika - UTM (2012)

5

Contoh Stored Procedure (contd-2) Contoh 3: Hapuslah pemain nomor 83 dari basisdata TENNIS. Akan tetapi jika pemain tersebut adalah kapten tim, maka pemain tidak boleh dihapus dari basisdata! 

Solusi PERTAMA: Eksekusi satu-per-satu Cek apakah pemain nomor 83 adalah kapten tim: SELECT COUNT(*) AS IS_CAPTAIN FROM TEAMS WHERE PLAYERNO = 83

Oleh karena pemain nomor 83 bukanlah kapten suatu tim, maka hapuslah semua data mengenai pemain tersebut: DELETE FROM MATCHES WHERE PLAYERNO = 83; DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 83; DELETE FROM PENALTIES WHERE PLAYERNO = 83; DELETE FROM PLAYERS WHERE PLAYERNO = 83; S1 T. Informatika - UTM (2012)

6

Contoh Stored Procedure

(contd-3)

Solusi KEDUA: Dengan stored procedure (tanpa parameter) CREATE PROCEDURE DELETE_PLAYER_83() BEGIN DECLARE NUMBER_OF_TEAMS INTEGER; SELECT COUNT(*) INTO NUMBER_OF_TEAMS FROM TEAMS WHERE PLAYERNO = 83;

END

IF NUMBER_OF_TEAMS = 0 THEN DELETE FROM MATCHES WHERE PLAYERNO = 83; DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 83; DELETE FROM PENALTIES WHERE PLAYERNO = 83; DELETE FROM PLAYERS WHERE PLAYERNO = 83; END IF;

Aktivasi/pemanggilan stored procedure: 

Menghapus stored procedure:

DROP PROCEDURE DELETE_PLAYER_83

CALL DELETE_PLAYER_83

S1 T. Informatika - UTM (2012)

7

Contoh Stored Procedure

(contd-4)

Solusi KETIGA: Stored procedure dengan parameter CREATE PROCEDURE DELETE_PLAYER (IN P_PLAYERNO INTEGER) BEGIN DECLARE NUMBER_OF_TEAMS INTEGER; SELECT COUNT(*) INTO NUMBER_OF_TEAMS FROM TEAMS WHERE PLAYERNO = P_PLAYERNO; IF NUMBER_OF_TEAMS = 0 THEN DELETE FROM MATCHES WHERE PLAYERNO = P_PLAYERNO; DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = P_PLAYERNO; DELETE FROM PENALTIES WHERE PLAYERNO = P_PLAYERNO; DELETE FROM PLAYERS WHERE PLAYERNO = P_PLAYERNO; END IF; END

Aktivasi/pemanggilan stored procedure: 

Menghapus stored procedure:

DROP PROCEDURE DELETE_PLAYER

CALL DELETE_PLAYER(83)

S1 T. Informatika - UTM (2012)

8

TRIGGER Create Trigger:  
trigger statement> ::= CREATE [ <definer option> ] TRIGGER <definer option> ::= DEFINER = { <user name> | CURRENT_USER } ::= BEFORE | AFTER ::= { INSERT | DELETE | UPDATE } ON FOR EACH ROW ::= <statement>

Menghapus Stored Procedure: ::= DROP TRIGGER [ . ]

S1 T. Informatika - UTM (2012)

9

Contoh Trigger Contoh 1: Buatlah tabel CHANGES!  

CREATE TABLE CHANGES (USER CHAR(30) NOT NULL, CHA_TIME TIMESTAMP NOT NULL, CHA_PLAYERNO SMALLINT NOT NULL, CHA_TYPE CHAR(1) NOT NULL, CHA_PLAYERNO_NEW INTEGER, PRIMARY KEY (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE));

Contoh 2: Buatlah trigger yang akan meng-update tabel CHANGES secara otomatis setiap kali ada penambahan baris baru di dalam tabel PLAYERS!  

CREATE TRIGGER INSERT_PLAYERS AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO,CHA_TYPE, CHA_PLAYERNO_NEW) VALUES (USER, CURDATE(), NEW.PLAYERNO, 'I', NULL); END S1 T. Informatika - UTM (2012)

10

Contoh Trigger (contd-2) Contoh 3: Trigger juga dapat memanggil stored procedure. Maka, trigger pada Contoh 2 dapat diubah/dipecah menjadi 2 bagian seperti berikut ini:  

CREATE PROCEDURE INSERT_CHANGE (IN CPNO INTEGER, IN CTYPE CHAR(1), IN CPNO_NEW INTEGER) BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_PLAYERNO_NEW) VALUES (USER, CURDATE(), CPNO, CTYPE, CPNO_NEW); END

CREATE TRIGGER INSERT_PLAYER AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE(NEW.PLAYERNO, 'I', NULL); END

S1 T. Informatika - UTM (2012)

11

Contoh Trigger (contd-3) Contoh 4: Buatlah trigger yang meng-update tabel CHANGES setiap kali ada baris di dalam tabel PLAYERS yang dihapus!  

CREATE TRIGGER DELETE_PLAYER AFTER DELETE ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE (OLD.PLAYERNO, 'D', NULL); END

Contoh 5: Buatlah trigger yang meng-update tabel CHANGES setiap kali ada baris di dalam tabel PLAYERS yang di-update!  

CREATE TRIGGER UPDATE_PLAYER AFTER UPDATE ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGES (NEW.PLAYERNO, 'U', OLD.PLAYERNO); END

S1 T. Informatika - UTM (2012)

12

Triggers: Integrity Constraints Contoh 6: Pastikan bahwa tahun kelahiran pemain adalah lebih kecil daripada tahun ketika ia bergabung di dalam klub! 

Solusi PERTAMA: Penggunaan Integrity Constraint ketika create tabel CREATE TABLE PLAYERS_Z (PLAYERNO SMALLINT NOT NULL, BIRTH_DATE DATE, JOINED SMALLINT NOT NULL, CHECK(YEAR(BIRTH_DATE) < JOINED))

Solusi KEDUA: Dengan Trigger CREATE TRIGGER BORN_VS_JOINED BEFORE INSERT, UPDATE ON PLAYERS_Z FOR EACH ROW BEGIN IF YEAR(NEW.BIRTH_DATE) >= NEW.JOINED) THEN ROLLBACK WORK; END IF; END S1 T. Informatika - UTM (2012)

13

Keterangan lebih detail mengenai:  Stored Procedure dan Trigger Cek MySQL 5.0 Reference Manual (Chapter 18. Stored Programs & Views) dan

SQL for MySQL Developers (PART IV. Procedural Database Objects) S1 T. Informatika - UTM (2012)

14

×

Report "Basis Data II. Pertemuan Ke-9 dan ke-10 (Stored Procedure dan Trigger) Noor Ifada S1 T. Informatika - UTM (2012) 1"

Your name

Email

Reason -Select Reason- Pornographic Defamatory Illegal/Unlawful Spam Other Terms Of Service Violation File a copyright complaint

Description

Close Send

×

Sign In

Email

Password

Remember me Forgot password?

Sign In

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close