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 ;

Komentar FB


Probably Related Article



3 comments:

!Rchymera! said...
January 10, 2010 at 5:42 AM

Thanks for share my friend and i like all about programing keep post and keep in touch for me...:D

Sales Generation Consultancy said...
March 11, 2010 at 10:23 PM

You have an amazing blog. Thanks for escalating my knowledge. I was not well-known with it. For more update I will be in touch.

金瓜米粉Jason said...
May 5, 2010 at 7:28 PM

徵信社,尋人,偵探,偵探社,徵才,私家偵探,徵信,徵信社,徵信公司,抓猴,出軌,背叛,婚姻,劈腿,感情,第三者,婚外情,一夜情,小老婆,外遇,市場調查,公平交易法,抓姦,債務,債務協商,應收帳款,詐欺,離婚,監護權,法律諮詢,法律常識,離婚諮詢,錄音,找人,追蹤器,GPS,徵信,徵信公司,尋人,抓姦,外遇,徵信,徵信社,徵信公司,尋人,抓姦,外遇,徵信,徵信社,徵信公司,尋人,抓姦,外遇,徵信,徵信社,徵信公司,尋人,抓姦,外遇,徵信社

Related Posts by Categories



Widget by