TRIGGER POSTGRESQL - BEFORE DELETE & UPDATE

CREATE OR REPLACE FUNCTION srv.fnc_history_kk_bantuan()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

    DECLARE

_kkBantuan   srv.th_kk_bantuan%rowtype;

BEGIN

    SELECT keterangan INTO _kkBantuan.jenis_bantuan FROM srv.tb_jenis_bantuan where id_jenis_bantuan = OLD.id_jenis_bantuan;

    SELECT (first_name || ' '|| middle_name || ' '|| last_name) INTO _kkBantuan.created_by FROM public.tb_user_profile where id_user = OLD.created_by;

INSERT INTO srv.th_kk_bantuan(

no_kk, jenis_bantuan, created_by, created_date)

VALUES (OLD.no_kk, _kkBantuan.jenis_bantuan, _kkBantuan.created_by, now());

RETURN OLD;

END;

$BODY$;

ALTER FUNCTION srv.fnc_history_kk_bantuan()

    OWNER TO postgres;

CREATE TRIGGER trg_history_kk_bantuan_bd

    BEFORE DELETE 

    ON srv.tb_kk_bantuan

    FOR EACH ROW

    EXECUTE PROCEDURE srv.fnc_history_kk_bantuan();

==========================================

                                JOIN NYA LUR

==========================================

CREATE OR REPLACE FUNCTION srv.fnc_history_kk_bantuan_terima()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

    DECLARE

_kkBantuanTerima srv.th_kk_bantuan_terima%rowtype;

BEGIN

SELECT B.keterangan, A.no_kk INTO _kkBantuanTerima.jenis_bantuan, _kkBantuanTerima.no_kk

FROM srv.tb_kk_bantuan A

JOIN srv.tb_jenis_bantuan B

ON A.id_jenis_bantuan = B.id_jenis_bantuan

where A.id_kk_bantuan = OLD.id_kk_bantuan;

    SELECT (first_name || ' '|| middle_name || ' '|| last_name) INTO _kkBantuanTerima.created_by FROM public.tb_user_profile where id_user = OLD.created_by;

INSERT INTO srv.th_kk_bantuan_terima(

no_kk, jenis_bantuan, nik_penerima, tanggal_terima, foto_bukti_terima, foto_terima, created_by, created_date)

VALUES (_kkBantuanTerima.no_kk, _kkBantuanTerima.jenis_bantuan, OLD.nik_penerima, OLD.tanggal_terima, OLD.foto_bukti_terima, OLD.foto_terima, _kkBantuanTerima.created_by, now());

RETURN OLD;

END;

$BODY$;

ALTER FUNCTION srv.fnc_history_kk_bantuan_terima()

    OWNER TO postgres;

CREATE TRIGGER trg_history_kk_bantuan_terima_bd

    BEFORE DELETE 

    ON srv.tb_kk_bantuan_terima

    FOR EACH ROW

EXECUTE PROCEDURE srv.fnc_history_kk_bantuan_terima();

========================================== 

                                JOIN NYA LUR

==========================================

ALTER TABLE srv.th_kk DROP COLUMN geom;

ALTER TABLE srv.th_kk ADD COLUMN geom geometry;

CREATE OR REPLACE FUNCTION srv.fnc_history_kk()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

    DECLARE

_kk srv.th_kk%rowtype;

BEGIN

SELECT B.keterangan, DESA.desa, DESA.kec, DESA.kab, DESA.prov, D.keterangan INTO _kk.status_kk, _kk.desa, _kk.kecamatan, _kk.kabupaten, _kk.provinsi, _kk.jenis_bantuan

FROM srv.tb_kk A

JOIN srv.tb_status_kk B

ON A.id_status_kk = B.id_status_kk

JOIN mst.vw_desa DESA

ON A.desa = DESA.kode

LEFT JOIN srv.tb_kk_bantuan C

ON A.no_kk = C.no_kk

LEFT JOIN srv.tb_jenis_bantuan D

ON C.id_jenis_bantuan = D.id_jenis_bantuan

where A.no_kk = OLD.no_kk;

    SELECT (first_name || ' '|| middle_name || ' '|| last_name) INTO _kk.created_by FROM public.tb_user_profile where id_user = OLD.created_by;

INSERT INTO srv.th_kk(

no_kk, status_kk, alamat, rt, rw, longitude, latitude, geom, desa, kecamatan, kabupaten, provinsi, kd_pos, foto_kk, foto_rumah, foto_keluarga, jenis_bantuan, penghasilan_tetap, penghasilan_rata2, pengeluaran_rata2, status, created_by, created_date)

VALUES (OLD.no_kk, _kk.status_kk, OLD.alamat, OLD.rt, OLD.rw, OLD.longitude, OLD.latitude, OLD.geom, _kk.desa, _kk.kecamatan, _kk.kabupaten, _kk.provinsi, OLD.kd_pos, OLD.foto_kk, OLD.foto_rumah, OLD.foto_keluarga, _kk.jenis_bantuan, OLD.penghasilan_tetap, OLD.penghasilan_rata2, OLD.pengeluaran_rata2, OLD.status, _kk.created_by, now());

RETURN OLD;

OLD.geom = ST_GeomFromText('POINT('||coalesce(NEW.longitude, OLD.longitude)||' '||coalesce(NEW.latitude, OLD.latitude)||')', 4326);

  RETURN OLD;

END;

$BODY$;

ALTER FUNCTION srv.fnc_history_kk()

    OWNER TO postgres;

CREATE TRIGGER trg_history_kk_bd

    BEFORE DELETE 

    ON srv.tb_kk

    FOR EACH ROW

EXECUTE PROCEDURE srv.fnc_history_kk();