Waw it's a big MySQL Trigger

I don't ever think to make a big MySQL Trigger. Maybe for seniors it just a little, but for me it was a big for a beggining..

After a long time learn about trigger, i finally implement in a project that need a real time summary data report direcly using MYSQL PL SQL programming.

There a programming way to do this, but it was a longer way and i dont like it. one way to cut this long way programming is using trigger that procces every inserting data, updating and deleting data..


the code below just a documentation to make a summary data.


DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_pengabdian`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_pengabdian` AFTER UPDATE ON `tb_pengabdian`
FOR EACH ROW BEGIN
DECLARE NEW_VALUE int;
DECLARE NEW_VALUE1 int;
DECLARE NEW_VALUE2 int;
DECLARE NEW_VALUE3 int;

DECLARE jumlah_ketua_peneliti numeric;
DECLARE jumlah_anggota_peneliti numeric;
DECLARE jumlah_peneliti_total numeric;


SELECT count(id) INTO NEW_VALUE FROM tb_pengabdian where id_status=1 and tahun=old.tahun;
SELECT sum(jumlah_dana) INTO NEW_VALUE1 FROM tb_pengabdian where id_status=1 and tahun=old.tahun;
SELECT count(id) INTO NEW_VALUE2 FROM tb_pengabdian where id_sumberdana=13 and tahun=old.tahun;
SELECT count(DISTINCT id_desabinaan) INTO NEW_VALUE3 FROM tb_pengabdian where tahun=old.tahun;

update tb_dwh_pengabdian set pengabdian_jum_pengabdian = NEW_VALUE where pengabdian_tahun=old.tahun;
update tb_dwh_pengabdian set pengabdian_jum_dana = NEW_VALUE1 where pengabdian_tahun=old.tahun;
update tb_dwh_pengabdian set Pengabdian_jum_kerjasama = NEW_VALUE2 where pengabdian_tahun=old.tahun;
update tb_dwh_pengabdian set pengabdian_jum_desa_binaan = NEW_VALUE3 where pengabdian_tahun=old.tahun;

update tb_mketua set status_disetujui=1 where id_ketua=old.id_ketua;
update tb_manggota set status_disetujui=1 where id_ketua=old.id_ketua;

SELECT COUNT(DISTINCT nip_ketua) INTO jumlah_ketua_peneliti FROM tb_mketua where status_disetujui=1 and tahun=old.tahun;
SELECT COUNT(DISTINCT nip_anggota) INTO jumlah_anggota_peneliti FROM tb_manggota where status_disetujui=1 and tahun=old.tahun;
set jumlah_peneliti_total= jumlah_ketua_peneliti+ jumlah_ketua_peneliti;

update tb_dwh_pengabdian set pengabdian_jum_dosen_mengabdi = jumlah_peneliti_total where pengabdian_tahun=old.tahun;

END;
$$

DELIMITER ;





DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_proposal`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_proposal` AFTER INSERT ON `tb_proposal`
FOR EACH ROW BEGIN
DECLARE NEW_VALUE int;
SELECT count(id) INTO NEW_VALUE FROM tb_proposal where tahun=new.tahun;

update tb_dwh_pengabdian set pengabdian_jum_proposal = NEW_VALUE where pengabdian_tahun=new.tahun;

END;
$$

DELIMITER ;

Posted in: PHPMySQL