Cara Membuat Trigger, Function dan Store Procedure

Contoh Kasus 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.

Pertama create dulu tabelnya :

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;

Kedua : Buat Trigger untuk menangani proses insert, update, delete

    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 ;

Ketiga : Function dan Store Procedure

    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 ;