PROCEDURE DAN TRIGGER MYSQL
BERKENALAN DENGAN STORE
PROCEDURE DAN TRIGGER
Stored Procedures which allow
us to automate or program our way out of many tasks on directly on the server
rather than having to write external scripts to do complex manipulation of data
– MYSQL References
Prosedur merupakan program yang
ditulis dan dieksekusi langsung dari Engine database. Beberapa situasi dimana
stored procedure sangat bermanfaat :
1.
Pada system yang didalamnya terdapat
bermacam-macam platform bahasa pemrograman, akan tetapi proses yang terjadi
pada database harus tetap memiliki standard yang sama.
2.
Sistem dikerjakan pada lingkungan yang
berbeda-beda dengan prioritas keamanan yang berbeda sehingga diperlukan
pembatasan terhadap hak-hak penggunaan terhadap tabel-tabel tertentu.
3.
Dengan adanya multi koneksi , maka dengan store
procedure akan memberikan performa yang lebih baik karena proses interkoneksi
dari luar server menjadi lebih sedikit.
4.
Programmer gak perlu pusing melihat
bertumpuk-tumpuk tabel karena hanya memakai prosedur yang sudah ada.
Sedangkan trigger merupakan store
procedure yang digunakan untuk memicu perubahan pada suatu tabel sesuai
dengan event yang terjadi (insert, delete, update). Trigger dapat
digunakan untuk memicu proses terhadap tabel lain maupun tabel itu
sendiri.
Manfaat dari trigger :
1.
Pada pemanfaatan program aplikasi multi
platform, proses bisnis dari sistem dapat dibuat langsung dilakukan didatabase
sehingga perbedaan interpretasi di program aplikasi menjadi lebih
diminimalisisr
2.
Proses lebih cepat
3.
Programmer tinggal ongkang-ongkang kaki
karena yang pusing DBAnya hehehehe..
Contoh tabel dan sampel data untuk
coba-coba trigger dan procedure
CREATE TABLE jurusan (
id CHAR(3),
nama VARCHAR(50) NOT NULL,
Constraint pk_jur primary key(id));
id CHAR(3),
nama VARCHAR(50) NOT NULL,
Constraint pk_jur primary key(id));
CREATE TABLE Mahasiswa (
Nim CHAR(7),
Nama VARCHAR(50) NOT NULL,
Tgl_lahir DATE,
Jn_Kelamin ENUM(‘L’,'P’),
jalan VARCHAR(50),
Kota VARCHAR(20),
jurusan varchar(3),
constraint pk_mhs primary key(nim),
constraint fk_mhs_jur foreign key(jurusan) references jurusan(id));
Nim CHAR(7),
Nama VARCHAR(50) NOT NULL,
Tgl_lahir DATE,
Jn_Kelamin ENUM(‘L’,'P’),
jalan VARCHAR(50),
Kota VARCHAR(20),
jurusan varchar(3),
constraint pk_mhs primary key(nim),
constraint fk_mhs_jur foreign key(jurusan) references jurusan(id));
CREATE TABLE Dosen (
Nip CHAR(5),
Nama VARCHAR(50) NOT NULL,
Tgl_lahir DATE,
Jn_Kelamin ENUM(‘L’,'P’),
jalan VARCHAR(50),
Kota VARCHAR(20),
jurusan varchar(3),
constraint pk_dsn primary key(nip),
constraint fk_dsn_jur foreign key(jurusan) references jurusan(id));
Nip CHAR(5),
Nama VARCHAR(50) NOT NULL,
Tgl_lahir DATE,
Jn_Kelamin ENUM(‘L’,'P’),
jalan VARCHAR(50),
Kota VARCHAR(20),
jurusan varchar(3),
constraint pk_dsn primary key(nip),
constraint fk_dsn_jur foreign key(jurusan) references jurusan(id));
CREATE TABLE MataKuliah (
Kode_mka CHAR(10) NOT NULL,
Nama VARCHAR(50) NOT NULL,
Semester CHAR(5) NoT NULL,
sks TINYINT NOT NULL,
jenis ENUM(‘P’,W’),
dosen char(5),
constraint pk_mka primary key(kode_mka),
constraint fk_mka_dsn foreign key(dosen) references dosen(nip));
Kode_mka CHAR(10) NOT NULL,
Nama VARCHAR(50) NOT NULL,
Semester CHAR(5) NoT NULL,
sks TINYINT NOT NULL,
jenis ENUM(‘P’,W’),
dosen char(5),
constraint pk_mka primary key(kode_mka),
constraint fk_mka_dsn foreign key(dosen) references dosen(nip));
CREATE TABLE Prasyarat(
ID_prasyarat int auto_increment,
Kode_prasyarat char(10),
Kode_mka char(10),
Nilai varchar(1),
constraint pk_pra primary key(id_prasyarat),
constraint fk_pra_mka foreign key(kode_mka) references matakuliah(kode_mka),
constraint fk_pra_pra foreign key(kode_prasyarat) references matakuliah(kode_mka));
ID_prasyarat int auto_increment,
Kode_prasyarat char(10),
Kode_mka char(10),
Nilai varchar(1),
constraint pk_pra primary key(id_prasyarat),
constraint fk_pra_mka foreign key(kode_mka) references matakuliah(kode_mka),
constraint fk_pra_pra foreign key(kode_prasyarat) references matakuliah(kode_mka));
CREATE TABLEkuliah (
id int(11) NOT NULL auto_increment,
nilai_angka tinyint(4)NULL,
nilai_huruf char(1)NULL,
bobot tinyint(4)NULL,
Th_akademik char(5) NOT NULL,
nim char(7)NULL,
mka char(10)NULL,
constraint pk_klh primary key(id),
constraint fk_klh_mhs foreign key(nim) references mahasiswa(nim),
constraint fk_klh_mka foreign key(nim) references matakuliah(kode_mka));
id int(11) NOT NULL auto_increment,
nilai_angka tinyint(4)NULL,
nilai_huruf char(1)NULL,
bobot tinyint(4)NULL,
Th_akademik char(5) NOT NULL,
nim char(7)NULL,
mka char(10)NULL,
constraint pk_klh primary key(id),
constraint fk_klh_mhs foreign key(nim) references mahasiswa(nim),
constraint fk_klh_mka foreign key(nim) references matakuliah(kode_mka));
INSERT INTO jurusan (Id, Nama)
VALUES
(‘J01′, ‘Ilmu Komputer’),
(‘J02′, ‘Teknologi Informasi’),
(‘J03′, ‘Manajemen Informatika’);
(‘J01′, ‘Ilmu Komputer’),
(‘J02′, ‘Teknologi Informasi’),
(‘J03′, ‘Manajemen Informatika’);
INSERT INTO Mahasiswa (Nim, Nama,
Tgl_lahir, Jn_Kelamin, jalan, Kota, jurusan) VALUES
(’0110001′, ‘Angga Ramadhan’, ’1986-05-05′, ‘L’, ‘Jl. Brigjen Katamso 50′, ‘Yogyakarta’, ‘J01′),
(’0110002′, ‘Amir syarifudin’, ’1985-06-20′, ‘L’, ‘Jl. Ahmad Yani 47′, ‘Sleman’, ‘J01′),
(’0110003′, ‘Dian Arum Ndalu’, ’1979-03-14′, ‘P’, ‘Jl. Kapten Tendean 23′, ‘Bantul’, ‘J01′),
(’0110004′, ‘Imam Bagus’, ’1980-05-13′, ‘L’, ‘Jl. Karangwaru I/3′, ‘Magelang’, ‘J01′),
(’0110005′, ‘Marlina Zein’, ’1982-02-01′, ‘P’, ‘Jl. Batang 108′, ‘Yogyakarta’, ‘J01′),
(’0110006′, ‘Rahendra Putra’, ’1986-04-12′, ‘L’, ‘Jl. Kenari 2A’, ‘Sleman’, ‘J01′), (’0110007′, ‘Yasinta Priska Nadia’, ’1978-04-29′, ‘P’, ‘Jl. Kertosono 87′, ‘Bandung’, ‘J01′),
(’0210001′, ‘Vivi Pratami’, ’1987-03-13′, ‘P’, ‘Jl. Gambiran 23′, ‘Yogyakarta’, ‘J02′),
(’0210002′, ‘Yuanita Tiara Reni’, ’1985-06-21′, ‘P’, ‘Jl. Mahameru 46′, ‘Jakarta’, ‘J02′),
(’0210003′, ‘Yusuf Nizar’, ’1978-09-23′, ‘L’, ‘Jl. Colombo’, ‘Yogyakarta’, ‘J02′),
(’0210004′, ‘Imelda Nurinda’, ’1984-08-12′, ‘P’, ‘Jl. Antasari 55′, ‘Medan’, ‘J02′),
(’0210005′, ‘Khomaruzaman’, ’1982-10-26′, ‘L’, ‘Jl. Wachid Hasyim 47′, ‘Makasar’, ‘J02′),
(’0210006′, ‘Danis Mariaman’, ’1979-12-18′, ‘L’, ‘Jl. Warungboto 05′, ‘Yogyakarta’, ‘J02′),
(’0210007′, ‘Ardhi Nugroho’, ’1986-09-16′, ‘L’, ‘Jl. Wonosari 63′, ‘Bantul’, ‘J02′),
(’0310001′, ‘Rima Nurasmi’, ’1987-03-03′, ‘P’, ‘Jl. Panggang Indah 108′, ‘Gunung Kidul’, ‘J03′),
(’0310002′, ‘Reza Fitriawati’, ’1985-07-10′, ‘P’, ‘Jl. Samas 2A’, ‘Bantul’, ‘J03′),
(’0310003′, ‘Haris Prasetya’, ’1980-06-28′, ‘L’, ‘Jl. Maga 23′, ‘Semarang’, ‘J03′),
(’0310004′, ‘Binti Nur Fitriani’, ’1986-09-14′, ‘P’, ‘Jl. Randu Belang 23′, ‘Yogyakarta’, ‘J03′),
(’0310005′, ‘Evi Munandari’, ’1988-01-21′, ‘P’, ‘Jl. Kebon Agung 40′, ‘Surakarta’, ‘J03′),
(’0310006′, ‘Nur Yusman Aqim’, ’1979-02-28′, ‘L’, ‘Jl. Kamboja 12′, ‘Magelang’, ‘J03′);
(’0110001′, ‘Angga Ramadhan’, ’1986-05-05′, ‘L’, ‘Jl. Brigjen Katamso 50′, ‘Yogyakarta’, ‘J01′),
(’0110002′, ‘Amir syarifudin’, ’1985-06-20′, ‘L’, ‘Jl. Ahmad Yani 47′, ‘Sleman’, ‘J01′),
(’0110003′, ‘Dian Arum Ndalu’, ’1979-03-14′, ‘P’, ‘Jl. Kapten Tendean 23′, ‘Bantul’, ‘J01′),
(’0110004′, ‘Imam Bagus’, ’1980-05-13′, ‘L’, ‘Jl. Karangwaru I/3′, ‘Magelang’, ‘J01′),
(’0110005′, ‘Marlina Zein’, ’1982-02-01′, ‘P’, ‘Jl. Batang 108′, ‘Yogyakarta’, ‘J01′),
(’0110006′, ‘Rahendra Putra’, ’1986-04-12′, ‘L’, ‘Jl. Kenari 2A’, ‘Sleman’, ‘J01′), (’0110007′, ‘Yasinta Priska Nadia’, ’1978-04-29′, ‘P’, ‘Jl. Kertosono 87′, ‘Bandung’, ‘J01′),
(’0210001′, ‘Vivi Pratami’, ’1987-03-13′, ‘P’, ‘Jl. Gambiran 23′, ‘Yogyakarta’, ‘J02′),
(’0210002′, ‘Yuanita Tiara Reni’, ’1985-06-21′, ‘P’, ‘Jl. Mahameru 46′, ‘Jakarta’, ‘J02′),
(’0210003′, ‘Yusuf Nizar’, ’1978-09-23′, ‘L’, ‘Jl. Colombo’, ‘Yogyakarta’, ‘J02′),
(’0210004′, ‘Imelda Nurinda’, ’1984-08-12′, ‘P’, ‘Jl. Antasari 55′, ‘Medan’, ‘J02′),
(’0210005′, ‘Khomaruzaman’, ’1982-10-26′, ‘L’, ‘Jl. Wachid Hasyim 47′, ‘Makasar’, ‘J02′),
(’0210006′, ‘Danis Mariaman’, ’1979-12-18′, ‘L’, ‘Jl. Warungboto 05′, ‘Yogyakarta’, ‘J02′),
(’0210007′, ‘Ardhi Nugroho’, ’1986-09-16′, ‘L’, ‘Jl. Wonosari 63′, ‘Bantul’, ‘J02′),
(’0310001′, ‘Rima Nurasmi’, ’1987-03-03′, ‘P’, ‘Jl. Panggang Indah 108′, ‘Gunung Kidul’, ‘J03′),
(’0310002′, ‘Reza Fitriawati’, ’1985-07-10′, ‘P’, ‘Jl. Samas 2A’, ‘Bantul’, ‘J03′),
(’0310003′, ‘Haris Prasetya’, ’1980-06-28′, ‘L’, ‘Jl. Maga 23′, ‘Semarang’, ‘J03′),
(’0310004′, ‘Binti Nur Fitriani’, ’1986-09-14′, ‘P’, ‘Jl. Randu Belang 23′, ‘Yogyakarta’, ‘J03′),
(’0310005′, ‘Evi Munandari’, ’1988-01-21′, ‘P’, ‘Jl. Kebon Agung 40′, ‘Surakarta’, ‘J03′),
(’0310006′, ‘Nur Yusman Aqim’, ’1979-02-28′, ‘L’, ‘Jl. Kamboja 12′, ‘Magelang’, ‘J03′);
INSERT INTO dosen (Nip, Nama,
Tgl_lahir, Jn_Kelamin, jalan, Kota, jurusan) VALUES
(’03002′, ‘Diana Ginting,S.Pd.,M.Cs.’, ’1959-06-21′, ”, ‘Jl. Gatot Subroto 89′, ‘Jakarta’, ‘J03′),
(’03001′, ‘Septian Dwiyanto,S.T.,M.Cs.’, ’1964-03-13′, ‘P’, ‘Jl. Bulukan 03′, ‘Lampung’, ‘J03′),
(’02003′, ‘Wahyu Nirba Habibah,S.T.’, ’1963-04-29′, ”, ‘Jl. Mayoret’, ‘Bandung’, ‘J02′),
(’02002′, ‘Jamalludin,S.T.,M.Kom.’, ’1965-04-12′, ‘P’, ‘Jl. Jend. Sudirman’, ‘Sleman’, ‘J02′),
(’02001′, ‘Mahmudin,S.Ag.,M.Kom.’, ’1956-02-01′, ‘P’, ‘Jl. Disvus 20′, ‘Yogyakarta’, ‘J02′),
(’01003′, ‘Kitri Sri Rahayu,S.T.’, ’1958-03-14′, ‘P’, ‘Jl. Mrican 23′, ‘Yogyakarta’, ‘J01′),
(’01004′, ‘Eko Aribowo,S.T.,M.Kom.’, ’1961-05-13′, ‘P’, ‘Jl. Mawar 56′, ‘Magelang’, ‘J01′),
(’01002′, ‘Imana Kusuma,S.T.,M.Kom.’, ’1960-06-20′, ‘L’, ‘Jl. KOl. Sugiono’, ‘Sleman’, ‘J01′),
(’01001′, ‘Wahyu Pujiono,S.T.,M.Kom.’, ’1957-09-03′, ‘L’, ‘Jl. Ambassador’, ‘Jakarta’, ‘J01′),
(’03003′, ‘Yohanes Surya,S.T.’, ’1960-09-23′, ‘P’, ‘Jl. Tamantirto’, ‘Bantul’, ‘J03′);
(’03002′, ‘Diana Ginting,S.Pd.,M.Cs.’, ’1959-06-21′, ”, ‘Jl. Gatot Subroto 89′, ‘Jakarta’, ‘J03′),
(’03001′, ‘Septian Dwiyanto,S.T.,M.Cs.’, ’1964-03-13′, ‘P’, ‘Jl. Bulukan 03′, ‘Lampung’, ‘J03′),
(’02003′, ‘Wahyu Nirba Habibah,S.T.’, ’1963-04-29′, ”, ‘Jl. Mayoret’, ‘Bandung’, ‘J02′),
(’02002′, ‘Jamalludin,S.T.,M.Kom.’, ’1965-04-12′, ‘P’, ‘Jl. Jend. Sudirman’, ‘Sleman’, ‘J02′),
(’02001′, ‘Mahmudin,S.Ag.,M.Kom.’, ’1956-02-01′, ‘P’, ‘Jl. Disvus 20′, ‘Yogyakarta’, ‘J02′),
(’01003′, ‘Kitri Sri Rahayu,S.T.’, ’1958-03-14′, ‘P’, ‘Jl. Mrican 23′, ‘Yogyakarta’, ‘J01′),
(’01004′, ‘Eko Aribowo,S.T.,M.Kom.’, ’1961-05-13′, ‘P’, ‘Jl. Mawar 56′, ‘Magelang’, ‘J01′),
(’01002′, ‘Imana Kusuma,S.T.,M.Kom.’, ’1960-06-20′, ‘L’, ‘Jl. KOl. Sugiono’, ‘Sleman’, ‘J01′),
(’01001′, ‘Wahyu Pujiono,S.T.,M.Kom.’, ’1957-09-03′, ‘L’, ‘Jl. Ambassador’, ‘Jakarta’, ‘J01′),
(’03003′, ‘Yohanes Surya,S.T.’, ’1960-09-23′, ‘P’, ‘Jl. Tamantirto’, ‘Bantul’, ‘J03′);
INSERT INTO matakuliah (Kode_mka,
Nama, Semester, sks, jenis, dosen) VALUES
(‘MI01002′, ‘Operating System’, ’1′, 2, ‘W’, ’03004′),
(‘MI01001′, ‘Konsep Basis Data’, ’1′, 2, ‘W’, ’03004′),
(‘IK02004′, ‘Basis Data’, ’2′, 3, ‘P’, ’01001′),
(‘IK02003′, ‘Arsitektur Komputer’, ’2′, 2, ‘P’, ’01004′),
(‘IK02002′, ‘Dasar Pemrograman II’, ’2′, 3, ‘W’, ’01002′),
(‘IK02001′, ‘Algoritma Pemrograman’, ’2′, 2, ‘W’, ’01003′),
(‘IK01004′, ‘E-Commerce’, ’1′, 2, ‘P’, ’01001′),
(‘IK01003′, ‘Organisasi Komputer’, ’1′, 2, ‘P’, ’01004′),
(‘IK01002′, ‘Dasar Pemrograman’, ’1′, 3, ‘W’, ’01003′),
(‘IK01001′, ‘Statistika & Probabilitas’, ’1′, 2, ‘W’, ’01002′),
(‘TI02004′, ‘Bahasa Rakitan’, ’1′, 2, ‘P’, ’02001′),
(‘TI02003′, ‘Kalkulus 2′, ’2′, 3, ‘W’, ’02003′),
(‘TI02002′, ‘Sistem Operasi’, ’2′, 2, ‘W’, ’02002′),
(‘TI02001′, ‘Algoritma Dan Pemrograman I’, ’2′, 3, ‘W’, ’02002′),
(‘TI01004′, ‘Matematika Diskrit’, ’1′, 2, ‘W’, ’02002′),
(‘TI01003′, ‘Kalkulus 1′, ’1′, 3, ‘W’, ’02003′),
(‘TI01002′, ‘Aljabar Linear’, ’1′, 3, ‘W’, ’02001′),
(‘TI01001′, ‘Statistik Dasar’, ’1′, 2, ‘W’, ’02001′),
(‘MI01003′, ‘Aljabar Elementer’, ’1′, 2, ‘W’, ’03003′),
(‘MI01004′, ‘Fuzzy Logic’, ’1′, 2, ‘P’, ’03001′),
(‘MI02001′, ‘Teknologi Basis Data’, ’2′, 2, ‘W’, ’03002′),
(‘MI02002′, ‘Sistem Operasi’, ’2′, 2, ‘W’, ’03001′),
(‘MI02003′, ‘Pemrograman Terstruktur’, ’3′, 2, ‘W’, ’03001′),
(‘MI02004′, ‘Web Programming’, ’2′, 3, ‘P’, ’03003′);
(‘MI01002′, ‘Operating System’, ’1′, 2, ‘W’, ’03004′),
(‘MI01001′, ‘Konsep Basis Data’, ’1′, 2, ‘W’, ’03004′),
(‘IK02004′, ‘Basis Data’, ’2′, 3, ‘P’, ’01001′),
(‘IK02003′, ‘Arsitektur Komputer’, ’2′, 2, ‘P’, ’01004′),
(‘IK02002′, ‘Dasar Pemrograman II’, ’2′, 3, ‘W’, ’01002′),
(‘IK02001′, ‘Algoritma Pemrograman’, ’2′, 2, ‘W’, ’01003′),
(‘IK01004′, ‘E-Commerce’, ’1′, 2, ‘P’, ’01001′),
(‘IK01003′, ‘Organisasi Komputer’, ’1′, 2, ‘P’, ’01004′),
(‘IK01002′, ‘Dasar Pemrograman’, ’1′, 3, ‘W’, ’01003′),
(‘IK01001′, ‘Statistika & Probabilitas’, ’1′, 2, ‘W’, ’01002′),
(‘TI02004′, ‘Bahasa Rakitan’, ’1′, 2, ‘P’, ’02001′),
(‘TI02003′, ‘Kalkulus 2′, ’2′, 3, ‘W’, ’02003′),
(‘TI02002′, ‘Sistem Operasi’, ’2′, 2, ‘W’, ’02002′),
(‘TI02001′, ‘Algoritma Dan Pemrograman I’, ’2′, 3, ‘W’, ’02002′),
(‘TI01004′, ‘Matematika Diskrit’, ’1′, 2, ‘W’, ’02002′),
(‘TI01003′, ‘Kalkulus 1′, ’1′, 3, ‘W’, ’02003′),
(‘TI01002′, ‘Aljabar Linear’, ’1′, 3, ‘W’, ’02001′),
(‘TI01001′, ‘Statistik Dasar’, ’1′, 2, ‘W’, ’02001′),
(‘MI01003′, ‘Aljabar Elementer’, ’1′, 2, ‘W’, ’03003′),
(‘MI01004′, ‘Fuzzy Logic’, ’1′, 2, ‘P’, ’03001′),
(‘MI02001′, ‘Teknologi Basis Data’, ’2′, 2, ‘W’, ’03002′),
(‘MI02002′, ‘Sistem Operasi’, ’2′, 2, ‘W’, ’03001′),
(‘MI02003′, ‘Pemrograman Terstruktur’, ’3′, 2, ‘W’, ’03001′),
(‘MI02004′, ‘Web Programming’, ’2′, 3, ‘P’, ’03003′);
INSERT INTO prasyarat
(ID_prasyarat, Kode_prasyarat, Kode_mka, Nilai) VALUES
(1, ‘IK02002′, ‘IK01002′, ‘C’),
(2, ‘IK02003′, ‘IK01003′, ‘C’),
(3, ‘MI02004′, ‘MI01001′, ‘C’),
(4, ‘MI02004′, ‘MI01002′, ‘C’);
(1, ‘IK02002′, ‘IK01002′, ‘C’),
(2, ‘IK02003′, ‘IK01003′, ‘C’),
(3, ‘MI02004′, ‘MI01001′, ‘C’),
(4, ‘MI02004′, ‘MI01002′, ‘C’);
INSERT INTO kuliah (nilai_angka,
nilai_huruf, bobot, Th_akademik, nim, mka) VALUES
(66, ‘B’, 3, ’20101′, ’0210003′, ‘TI01001′),
(50, ‘C’, 2, ’20101′, ’0210002′, ‘TI01004′),
(90, ‘A’, 4, ’20101′, ’0210003′, ‘TI01003′),
(70, ‘B’, 3, ’20101′, ’0210001′, ‘TI01001′),
(80, ‘B’, 3, ’20101′, ’0110002′, ‘IK01004′),
(95, ‘A’, 4, ’20101′, ’0110001′, ‘IK01004′),
(70, ‘B’, 3, ’20101′, ’0110001′, ‘IK01003′),
(80, ‘B’, 3, ’20101′, ’0110001′, ‘IK01001′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02001′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02003′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02004′),
(95, ‘A’, 4, ’20102′, ’0110002′, ‘IK02004′),
(NULL, NULL, NULL, ’20102′, ’0210001′, ‘TI02001′),
(NULL, NULL, NULL, ’20102′, ’0210003′, ‘TI02003′),
(NULL, NULL, NULL, ’20102′, ’0210002′, ‘TI02004′),
(NULL, NULL, NULL, ’20102′, ’0210003′, ‘TI02001′),
(NULL, NULL, NULL, ’20102′, ’0110002′, ‘IK02001′);
(66, ‘B’, 3, ’20101′, ’0210003′, ‘TI01001′),
(50, ‘C’, 2, ’20101′, ’0210002′, ‘TI01004′),
(90, ‘A’, 4, ’20101′, ’0210003′, ‘TI01003′),
(70, ‘B’, 3, ’20101′, ’0210001′, ‘TI01001′),
(80, ‘B’, 3, ’20101′, ’0110002′, ‘IK01004′),
(95, ‘A’, 4, ’20101′, ’0110001′, ‘IK01004′),
(70, ‘B’, 3, ’20101′, ’0110001′, ‘IK01003′),
(80, ‘B’, 3, ’20101′, ’0110001′, ‘IK01001′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02001′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02003′),
(NULL, NULL, NULL, ’20102′, ’0110001′, ‘IK02004′),
(95, ‘A’, 4, ’20102′, ’0110002′, ‘IK02004′),
(NULL, NULL, NULL, ’20102′, ’0210001′, ‘TI02001′),
(NULL, NULL, NULL, ’20102′, ’0210003′, ‘TI02003′),
(NULL, NULL, NULL, ’20102′, ’0210002′, ‘TI02004′),
(NULL, NULL, NULL, ’20102′, ’0210003′, ‘TI02001′),
(NULL, NULL, NULL, ’20102′, ’0110002′, ‘IK02001′);
A. TRIGGER
Contoh Trigger yang digunakan untuk
memicu perubahan pada tabel kuliah, pada tabel kuliah proses insert diasumsikan
hanya dilakukan pada kolom nilai_angka saja sedangkan nilai huruf dan bobot
akan terisi secara otomatis (dg mekanisme trigger) dilakukan jika nilai_angka
terisi dengan kriteria tertentu.
Trigger disini dilakukan ketika
terjadi 2 event yaitu insert (Before Insert) pada nilai_angka atau update
(before update) pada nilai_angka.
Kalo di oracle bisa dibuat satu
saja tapi kok di mysql aku coba gak bisa 
, terpaksa bikin 2 trigger.
, terpaksa bikin 2 trigger.
DELIMITER &&
CREATE TRIGGER updatebobot before update on kuliah
for each row
begin
if new.nilai_angka < 20 then
set new.bobot = 0;
set new.nilai_huruf=’E’;
else if new.nilai_angka >= 20 and new.nilai_angka <=40 then
set new.bobot = 1;
set new.nilai_huruf=’D’;
else if new.nilai_angka >= 41 and new.nilai_angka <=60 then
set new.bobot = 2;
set new.nilai_huruf=’C’;
else if new.nilai_angka >= 61 and new.nilai_angka <=80 then
set new.bobot = 3;
set new.nilai_huruf=’B’;
else if new.nilai_angka >= 81 and new.nilai_angka <=100 then
set new.bobot = 4;
set new.nilai_huruf=’A’;
end if;
end if;
end if;
end if;
end if;
end &&
DELIMITER ;
CREATE TRIGGER updatebobot before update on kuliah
for each row
begin
if new.nilai_angka < 20 then
set new.bobot = 0;
set new.nilai_huruf=’E’;
else if new.nilai_angka >= 20 and new.nilai_angka <=40 then
set new.bobot = 1;
set new.nilai_huruf=’D’;
else if new.nilai_angka >= 41 and new.nilai_angka <=60 then
set new.bobot = 2;
set new.nilai_huruf=’C’;
else if new.nilai_angka >= 61 and new.nilai_angka <=80 then
set new.bobot = 3;
set new.nilai_huruf=’B’;
else if new.nilai_angka >= 81 and new.nilai_angka <=100 then
set new.bobot = 4;
set new.nilai_huruf=’A’;
end if;
end if;
end if;
end if;
end if;
end &&
DELIMITER ;
DELIMITER &&
CREATE TRIGGER insertbobot before insert on kuliah
for each row
begin
if new.nilai_angka < 20 then
set new.bobot = 0;
set new.nilai_huruf=’E’;
else if new.nilai_angka >= 20 and new.nilai_angka <=40 then
set new.bobot = 1;
set new.nilai_huruf=’D’;
else if new.nilai_angka >= 41 and new.nilai_angka <=60 then
set new.bobot = 2;
set new.nilai_huruf=’C’;
else if new.nilai_angka >= 61 and new.nilai_angka <=80 then
set new.bobot = 3;
set new.nilai_huruf=’B’;
else if new.nilai_angka >= 81 and new.nilai_angka <=100 then
set new.bobot = 4;
set new.nilai_huruf=’A’;
end if;
end if;
end if;
end if;
end if;
end &&
DELIMITER ;
CREATE TRIGGER insertbobot before insert on kuliah
for each row
begin
if new.nilai_angka < 20 then
set new.bobot = 0;
set new.nilai_huruf=’E’;
else if new.nilai_angka >= 20 and new.nilai_angka <=40 then
set new.bobot = 1;
set new.nilai_huruf=’D’;
else if new.nilai_angka >= 41 and new.nilai_angka <=60 then
set new.bobot = 2;
set new.nilai_huruf=’C’;
else if new.nilai_angka >= 61 and new.nilai_angka <=80 then
set new.bobot = 3;
set new.nilai_huruf=’B’;
else if new.nilai_angka >= 81 and new.nilai_angka <=100 then
set new.bobot = 4;
set new.nilai_huruf=’A’;
end if;
end if;
end if;
end if;
end if;
end &&
DELIMITER ;
Untuk mencoba trigger :
UPDATE KULIAH set nilai_angka =60
where nim =’0110001′ and mka =’IK02001′ and th_akademik=’20102′;
INSERT INTO kuliah (nilai_angka, Th_akademik, nim, mka) VALUES (66, ’20102′, ’010004′, ‘TI01004′);
INSERT INTO kuliah (nilai_angka, Th_akademik, nim, mka) VALUES (66, ’20102′, ’010004′, ‘TI01004′);
B. PROCEDURE
Contoh procedure untuk melakukan
cetak KRS, cetak KHS dan melakukan Proses KRS (input KRS) dan juga Input Nilai.
a) Procedure cetak KRS
DELIMITER &&
CREATE PROCEDURE CetakKRS(nim char(7), ta char(7))
BEGIN
SELECT distinct concat(m.nim ,”, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa m
inner join kuliah k on m.nim = k.nim
where k.nim =nim and k.Th_akademik =ta;
SELECT concat(k.mka ,’ | ‘ , mk.nama, ‘ ‘) as “Mata Kuliah”, d.nama as “Dosen”, mk.sks as “SKS” from kuliah k
inner join matakuliah mk on k.mka = mk.kode_mka
inner join mahasiswa mhs on k.nim = mhs.nim
inner join dosen d on d.nip = mk.dosen
where k.nim = nim and k.Th_akademik = ta;
SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan” from kuliah k
inner join mahasiswa m on k.nim = m.nim
inner join matakuliah mk on k.mka = mk.kode_mka
where k.nim = nim and k.Th_akademik = ta
group by m.nim ;
END &&
DELIMITER ;
CREATE PROCEDURE CetakKRS(nim char(7), ta char(7))
BEGIN
SELECT distinct concat(m.nim ,”, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa m
inner join kuliah k on m.nim = k.nim
where k.nim =nim and k.Th_akademik =ta;
SELECT concat(k.mka ,’ | ‘ , mk.nama, ‘ ‘) as “Mata Kuliah”, d.nama as “Dosen”, mk.sks as “SKS” from kuliah k
inner join matakuliah mk on k.mka = mk.kode_mka
inner join mahasiswa mhs on k.nim = mhs.nim
inner join dosen d on d.nip = mk.dosen
where k.nim = nim and k.Th_akademik = ta;
SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan” from kuliah k
inner join mahasiswa m on k.nim = m.nim
inner join matakuliah mk on k.mka = mk.kode_mka
where k.nim = nim and k.Th_akademik = ta
group by m.nim ;
END &&
DELIMITER ;
b) Procedure cetak KHS
DELIMITER &&
CREATE PROCEDURE CetakKHS(nim char(7), ta char(7))
BEGIN
SELECT distinct concat(m.nim ,’ ‘, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa m
inner join kuliah k on m.nim = k.nim
where k.nim =nim and k.Th_akademik =ta;
CREATE PROCEDURE CetakKHS(nim char(7), ta char(7))
BEGIN
SELECT distinct concat(m.nim ,’ ‘, m.nama ,’ | TA : ‘, k.th_akademik) as “Data Mahasiswa” from mahasiswa m
inner join kuliah k on m.nim = k.nim
where k.nim =nim and k.Th_akademik =ta;
SELECT k.mka, mk.nama, mk.sks,
k.nilai_huruf as Nilai, k.bobot, (k.bobot * mk.sks) as “bobot (bobot x sks)”
from kuliah k
inner join matakuliah mk on k.mka = mk.kode_mka
inner join mahasiswa mhs on k.nim = mhs.nim
where k.nim = nim and k.Th_akademik = ta;
SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan “, (sum(k.bobot * mk.sks)) as “Total Bobot”, (sum(k.bobot * mk.sks)/ sum(mk.sks)) as “IP
Sementara” from kuliah k
inner join mahasiswa m on k.nim = m.nim
inner join matakuliah mk on k.mka = mk.kode_mka
where k.nim = nim and k.Th_akademik = ta
group by m.nim ;
END &&
DELIMITER ;
inner join matakuliah mk on k.mka = mk.kode_mka
inner join mahasiswa mhs on k.nim = mhs.nim
where k.nim = nim and k.Th_akademik = ta;
SELECT Concat(‘Jumlah SKS = ‘, sum(mk.sks)) as ” Keterangan “, (sum(k.bobot * mk.sks)) as “Total Bobot”, (sum(k.bobot * mk.sks)/ sum(mk.sks)) as “IP
Sementara” from kuliah k
inner join mahasiswa m on k.nim = m.nim
inner join matakuliah mk on k.mka = mk.kode_mka
where k.nim = nim and k.Th_akademik = ta
group by m.nim ;
END &&
DELIMITER ;
c) Proses insert dan
update tabel kuliah pada trigger diatas dapat juga dijadikan procedure
DELIMITER &&
CREATE PROCEDURE InputKRS(nr char(7), mk char(10), ta char(5))
BEGIN
Insert into kuliah(nim , mka, th_akademik)
values (nr, mk, ta);
END &&
DELIMITER ;
CREATE PROCEDURE InputKRS(nr char(7), mk char(10), ta char(5))
BEGIN
Insert into kuliah(nim , mka, th_akademik)
values (nr, mk, ta);
END &&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE InputNilai(nr char(7), mk char(10), ta char(5), nl tinyint)
BEGIN
Update kuliah set nilai_angka = nl
where kuliah.nim =nr and kuliah.mka = mk and kuliah.th_akademik = ta;
END &&
DELIMITER ;
CREATE PROCEDURE InputNilai(nr char(7), mk char(10), ta char(5), nl tinyint)
BEGIN
Update kuliah set nilai_angka = nl
where kuliah.nim =nr and kuliah.mka = mk and kuliah.th_akademik = ta;
END &&
DELIMITER ;
Melihat status dari procedure yang
aktif
SHOW PROCEDURE STATUS;
Memanggil Procedure yang sudah
dibuat
CALL CETAKKRS(’0110001′,’20102′);
CALL CETAKKHS(’0110001′,’20101′);
CALL CETAKKHS(’0110001′,’20101′);
Tidak ada komentar:
Posting Komentar