Trigger dan Stored Procedure pada Nilai Terbaik per Kode Matakuliah

Contoh kasus kali ini adalah tentang pengisian tabel nilai terbaik dibantu dengan trigger ketika ada perubahan yang terjadi pada tabel nilai. Alasannya, agar tidak perlu menghabiskan banyak sumber daya pada waktu mencari nilai terbaik (untuk menghitung ipk, membuat transkrip nilai, dan sebagainya). Sebagai tambahan, tidak semua mahasiswa itu reguler, kadang ada mahasiswa yang membawa nilai (mahasiswa pindahan atau mahasiswa yang melanjutkan studi), nilai-nilai tersebut dikonversi ke dalam matakuliah yang diakui atau lebih dikenal dengan nilai penyetaraan (equivalensi0, sehingga mahasiswa tersebut tinggal menempuh sisanya, dengan catatan: nilai equivalensi tidak dapat diperbaiki atau diulang.

struktur tabel:

USE simptt;

CREATE TABLE `simptt`.`ak_krsnilai` (
`kdmahasiswa` int(10) unsigned NOT NULL DEFAULT '0',
`kdpenawaran` int(10) unsigned DEFAULT '0',
`nilai` varchar(2) DEFAULT 'X',
`nilaiangka` tinyint(3) unsigned DEFAULT '0',
`kdkrsnilai` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kdtahunakademik` mediumint(8) unsigned NOT NULL DEFAULT '0',
`sxn` tinyint(3) unsigned DEFAULT '0',
`sks` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`kdkrsnilai`),
KEY `FK1` (`kdtahunakademik`),
KEY `FK2` (`kdpenawaran`),
KEY `FK3` (`kdmahasiswa`)
) ENGINE=MyISAM;

CREATE TABLE `simptt`.`ak_krsnilai_terbaik` (
`kdkrsnilai` bigint(20) unsigned NOT NULL,
`kdmatakuliah` mediumint(8) unsigned NOT NULL,
`kodematakuliah` varchar(10) NOT NULL,
`kdmahasiswa` int(10) unsigned NOT NULL,
`nilai` varchar(2) NOT NULL,
`nilaiangka` tinyint(3) unsigned NOT NULL,
`sks` tinyint(3) unsigned NOT NULL,
`sxn` tinyint(3) unsigned NOT NULL,
`isequivalen` tinyint(1) unsigned NOT NULL,
PRIMARY KEY (`kdkrsnilai`),
KEY `FK1` (`kodematakuliah`),
KEY `FK2` (`kdmahasiswa`),
KEY `FK3` (`kdmatakuliah`)
) ENGINE=MyISAM;

CREATE TABLE  `simptt`.`ak_krsnilai_equivalensi` (
  `kdkrsnilai` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `kdmatakuliah` int(10) unsigned NOT NULL,
  `kodematakuliah` varchar(10) NOT NULL,
  `kdmahasiswa` int(10) unsigned NOT NULL,
  `nilai` varchar(2) NOT NULL,
  `nilaiangka` tinyint(3) unsigned NOT NULL,
  `sks` tinyint(3) unsigned NOT NULL,
  `sxn` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`kdkrsnilai`),
  KEY `FK1` (`kodematakuliah`),
  KEY `FK2` (`kdmahasiswa`),
  KEY `FK3` (`kdmatakuliah`)
) ENGINE=MyISAM AUTO_INCREMENT=1;


CREATE TABLE `simptt`.`ak_matakuliah` (
`kdmatakuliah` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`kodematakuliah` varchar(10) NOT NULL DEFAULT '',
`matakuliah` varchar(100) DEFAULT NULL,
`sks` tinyint(3) unsigned NOT NULL DEFAULT '0',
`skst` tinyint(3) unsigned NOT NULL DEFAULT '0',
`sksp` tinyint(3) unsigned NOT NULL DEFAULT '0',
`sksl` tinyint(3) unsigned NOT NULL DEFAULT '0',
`kdkurikulum` smallint(5) unsigned NOT NULL,
`status` varchar(1) NOT NULL DEFAULT 'A',
`semester` tinyint(2) NOT NULL DEFAULT '1',
PRIMARY KEY (`kdmatakuliah`),
KEY `FK1` (`kodematakuliah`)
) ENGINE=MyISAM ;


CREATE TABLE `simptt`.`ak_penawaranmatakuliah` (
`kdpenawaran` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kdmatakuliah` mediumint(8) unsigned NOT NULL DEFAULT '0',
`kelas` varchar(3) NOT NULL,
`kapasitas` smallint(5) unsigned NOT NULL DEFAULT '0',
`kdtahunakademik` smallint(5) unsigned NOT NULL,
`kdjeniskelas` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`kdpenawaran`),
KEY `FK1` (`kdtahunakademik`),
KEY `FK2` (`kdjeniskelas`),
KEY `FK3` (`kdmatakuliah`)
) ENGINE=MyISAM;

Trigger:

USE simptt;

DELIMITER $

CREATE DEFINER = `root`@`localhost` TRIGGER `simptt`.`ak_krsnilai_setnilai_` BEFORE INSERT ON `ak_krsnilai` FOR EACH ROW
BEGIN
set NEW.nilaiangka := f_nilaiangka(NEW.nilai);
set NEW.sks := (select sql_cache sks from ak_matakuliah m join ak_penawaranmatakuliah p on (m.kdmatakuliah=p.kdmatakuliah) where kdpenawaran=NEW.kdpenawaran);
set NEW.sxn := NEW.nilaiangka * NEW.sks;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER `simptt`.`ak_krsnilai_setnilai` AFTER INSERT ON `ak_krsnilai` FOR EACH ROW
BEGIN
declare matakuliah_ int;
declare sks_ tinyint;
declare nilaiangka_ tinyint;
declare kodematakuliah_ varchar(10);
declare kdmatakuliah_ int;
declare nilai_ varchar(2);
declare sxn_ smallint;

#cari tahu kodematakuliah
set kodematakuliah_ := (select sql_cache kodematakuliah from ak_matakuliah m join ak_penawaranmatakuliah p on p.kdmatakuliah=m.kdmatakuliah where kdpenawaran=NEW.kdpenawaran);
set kdmatakuliah_ := (select sql_cache kdmatakuliah from ak_penawaranmatakuliah p where kdpenawaran=NEW.kdpenawaran);

#apakah kodematakuliah ada?
if (select count(kdkrsnilai) from ak_krsnilai_terbaik where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa) > 0 then #kodematakuliah ada
begin
#tentukan nilai dan sks terbaik
set sks_ := (select ifnull(max(k.sks), 0) from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa);
set nilai_ := (select ifnull(min(k.nilai), 'X') from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa);

#kalibrasi nilai dan sks
if (sks_ <> NEW.nilai) then set nilai_ := NEW.nilai; end if;

#hitung nilai angka dan sxn
set nilaiangka_ := f_nilaiangka(nilai_);
set sxn_ := sks_ * nilaiangka_;

#update nilai
update ak_krsnilai_terbaik set nilai=nilai_, nilaiangka=nilaiangka_, sks=sks_, sxn=sxn_ where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa;
end;
elseif not isnull(kodematakuliah_) then #kodematakuliah tidak ada
insert into ak_krsnilai_terbaik values (NEW.kdkrsnilai, kdmatakuliah_, kodematakuliah_, NEW.kdmahasiswa, NEW.nilai, NEW.nilaiangka, NEW.sks, NEW.sxn);
end if;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER `simptt`.`ak_krsnilai_ubahnilai_` BEFORE UPDATE ON `ak_krsnilai` FOR EACH ROW
BEGIN
set NEW.nilaiangka := f_nilaiangka(NEW.nilai);
set NEW.sxn := NEW.nilaiangka * NEW.sks;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER `simptt`.`ak_krsnilai_ubahnilai` AFTER UPDATE ON `ak_krsnilai` FOR EACH ROW
BEGIN
declare matakuliah_ int;
declare sks_ tinyint;
declare nilaiangka_ tinyint;
declare kodematakuliah_ varchar(10);
declare kdmatakuliah_ int;
declare nilai_ varchar(2);
declare sxn_ smallint;

#cari tahu kodematakuliah
set kodematakuliah_ := (select sql_cache kodematakuliah from ak_matakuliah m join ak_penawaranmatakuliah p on p.kdmatakuliah=m.kdmatakuliah where kdpenawaran=NEW.kdpenawaran);
set kdmatakuliah_ := (select sql_cache kdmatakuliah from ak_penawaranmatakuliah p where kdpenawaran=NEW.kdpenawaran);

#apakah kodematakuliah ada?
if (select count(kdkrsnilai) from ak_krsnilai_terbaik where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa) > 0 then #kodematakuliah ada
begin
#tentukan nilai dan sks terbaik
set sks_ := (select ifnull(max(k.sks), 0) from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa);
set nilai_ := (select ifnull(min(k.nilai), 'X') from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa);

#kalibrasi nilai dan sks
if (sks_ <> NEW.nilai) then set nilai_ := NEW.nilai; end if;

#hitung nilai angka dan sxn
set nilaiangka_ := f_nilaiangka(nilai_);
set sxn_ := sks_ * nilaiangka_;

#update nilai
update ak_krsnilai_terbaik set nilai=nilai_, nilaiangka=nilaiangka_, sks=sks_, sxn=sxn_ where kodematakuliah=kodematakuliah_ and kdmahasiswa=NEW.kdmahasiswa;
end;
elseif not isnull(kodematakuliah_) then #kodematakuliah tidak ada
insert into ak_krsnilai_terbaik values (NEW.kdkrsnilai, kdmatakuliah_, kodematakuliah_, NEW.kdmahasiswa, NEW.nilai, NEW.nilaiangka, NEW.sks, NEW.sxn);
end if;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER `simptt`.`ak_krsnilai_hapusnilai` AFTER DELETE ON `ak_krsnilai` FOR EACH ROW
BEGIN
declare matakuliah_ int;
declare sks_ tinyint;
declare nilaiangka_ tinyint;
declare kodematakuliah_ varchar(10);
declare nilai_ varchar(2);
declare sxn_ smallint;

#cari tahu kodematakuliah
set kodematakuliah_ := (select sql_cache kodematakuliah from ak_matakuliah m join ak_penawaranmatakuliah p on p.kdmatakuliah=m.kdmatakuliah where kdpenawaran=OLD.kdpenawaran);

#apakah kodematakuliah ada?
if (select count(kdkrsnilai) from ak_krsnilai_terbaik where kodematakuliah=kodematakuliah_ and kdmahasiswa=OLD.kdmahasiswa) > 0 then #kodematakuliah ada
begin
#tentukan nilai dan sks terbaik
set sks_ := (select ifnull(max(k.sks), 0) from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=OLD.kdmahasiswa);
set nilai_ := (select ifnull(min(k.nilai), 'X') from ak_krsnilai k join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran join ak_matakuliah m on m.kdmatakuliah=p.kdmatakuliah where kodematakuliah=kodematakuliah_ and kdmahasiswa=OLD.kdmahasiswa);

#hitung nilai angka dan sxn
set nilaiangka_ := f_nilaiangka(nilai_);
set sxn_ := sks_ * nilaiangka_;

#update nilai
update ak_krsnilai_terbaik set nilai=nilai_, nilaiangka=nilaiangka_, sks=sks_, sxn=sxn_ where kodematakuliah=kodematakuliah_ and kdmahasiswa=OLD.kdmahasiswa;
end;
elseif not isnull(kodematakuliah_) then #kodematakuliah tidak ada
delete from ak_krsnilai_terbaik where kodematakuliah=kodematakuliah_ and kdmahasiswa=OLD.kdmahasiswa;
end if;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER  `simptt`.`ak_krsnilai_equivalensi_setnilai_` BEFORE INSERT ON `ak_krsnilai_equivalensi` FOR EACH ROW
BEGIN
set NEW.nilaiangka := f_nilaiangka(NEW.nilai);
set NEW.sks        := (select sql_cache sks from ak_matakuliah where kdmatakuliah=NEW.kdmatakuliah);
set NEW.sxn        := NEW.nilaiangka * NEW.sks;
END $

CREATE DEFINER = `root`@`localhost` TRIGGER  `simptt`.`ak_krsnilai_equivalensi_setnilai` AFTER INSERT ON `ak_krsnilai_equivalensi` FOR EACH ROW
BEGIN
insert into ak_krsnilai_terbaik values (9999999999999999-NEW.kdkrsnilai, NEW.kdmatakuliah, NEW.kodematakuliah, NEW.kdmahasiswa, NEW.nilai, NEW.nilaiangka, NEW.sks, NEW.sxn, 1);
END $

CREATE DEFINER = `root`@`localhost` TRIGGER  `simptt`.`ak_krsnilai_equivalensi_ubahnilai_` BEFORE UPDATE ON `ak_krsnilai_equivalensi` FOR EACH ROW
BEGIN
set NEW.nilaiangka := f_nilaiangka(NEW.nilai);
set NEW.sxn        := NEW.nilaiangka * NEW.sks;
update ak_krsnilai_terbaik set nilai=NEW.nilai, nilaiangka=NEW.nilaiangka, sks=NEW.sks, sxn=NEW.sxn, kodematakuliah=NEW.kodematakuliah, kdmatakuliah=NEW.kdmatakuliah where kdkrsnilai=(9999999999999999-NEW.kdkrsnilai);
END $

CREATE DEFINER = `root`@`localhost` TRIGGER  `simptt`.`ak_krsnilai_equivalensi_hapusnilai` AFTER DELETE ON `ak_krsnilai_equivalensi` FOR EACH ROW
BEGIN
  delete from ak_krsnilai_terbaik where kdkrsnilai=(9999999999999999-OLD.kdkrsnilai);
END $

DELIMITER ;

function dan prosedur:

USE simptt;

DELIMITER $

CREATE DEFINER=`root`@`localhost` FUNCTION `f_nilaiangka`(nilaihuruf CHAR(2)) RETURNS tinyint(4)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE nilai_ CHAR(1);
SET nilai_ := (SELECT LCASE(LEFT(nilaihuruf, 1)));
CASE nilai_
WHEN "a" THEN RETURN 4;
WHEN "b" THEN RETURN 3;
WHEN "c" THEN RETURN 2;
WHEN "d" THEN RETURN 1;
ELSE RETURN 0;
END CASE;
END$

DELIMITER ;