TRIGGER FUNCTION USING GLOBAL - BEFORE DELETE & UPDATE

 

Pertama membuat function

berikut kode nya

CREATE OR REPLACE FUNCTION srv.fnc_save_history_kk(idata srv.tb_kk)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
        DECLARE
	_idata	srv.th_kk%rowtype;
BEGIN
	SELECT B.keterangan, DESA.desa, DESA.kec, DESA.kab, DESA.prov, D.keterangan INTO _idata.status_kk, _idata.desa, _idata.kecamatan, _idata.kabupaten, _idata.provinsi, _idata.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 = idata.no_kk;
	
	SELECT (coalesce(first_name, '') || ' '|| coalesce(middle_name, '') || ' '|| last_name) INTO _idata.created_by FROM public.tb_user_profile where id_user = idata.created_by;
	
	INSERT INTO srv.th_kk(
	no_kk, status_kk, alamat, rt, rw, longitude, latitude, 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, geom, delete_by, delete_date)
	VALUES (idata.no_kk, _idata.status_kk, idata.alamat, idata.rt, idata.rw, idata.longitude, idata.latitude, _idata.desa, _idata.kecamatan, _idata.kabupaten, _idata.provinsi, idata.kd_pos, idata.foto_kk, idata.foto_rumah, idata.foto_keluarga, _idata.jenis_bantuan, idata.penghasilan_tetap, idata.penghasilan_rata2, idata.pengeluaran_rata2, idata.status, idata.created_by, idata.created_date, idata.geom, idata.delete_by, idata.delete_date);
END;
$BODY$;

ALTER FUNCTION srv.fnc_save_history_kk(srv.tb_kk)
    OWNER TO postgres;

Berikut ini ialah cara memanggil function untuk trigger UPDATE nya :

    CREATE OR REPLACE FUNCTION srv.fnc_update_kk()
        RETURNS trigger
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE NOT LEAKPROOF
    AS $BODY$
        DECLARE
    vRet character varying;
    BEGIN

        NEW.delete_date = NULL;
	IF NEW.delete_by IS NULL THEN 
	vRet = srv.fnc_save_history_kk(NEW);
	END IF;
	RETURN NEW;

    END;
    $BODY$;

    ALTER FUNCTION srv.fnc_update_kk()
        OWNER TO postgres;

    CREATE TRIGGER trg_kk_bu
        BEFORE UPDATE 
        ON srv.tb_kk
        FOR EACH ROW
    EXECUTE PROCEDURE srv.fnc_update_kk();
    

Berikut ini ialah cara memanggil function untuk trigger nya :

   CREATE OR REPLACE FUNCTION srv.fnc_delete_kk()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
    DECLARE
vRet character varying;
BEGIN
	vRet = srv.fnc_save_history_kk(OLD);
	RETURN OLD;
END;
$BODY$;

ALTER FUNCTION srv.fnc_delete_kk()
    OWNER TO postgres;

CREATE TRIGGER trg_kk_bd
    BEFORE DELETE 
    ON srv.tb_kk
    FOR EACH ROW
EXECUTE PROCEDURE srv.fnc_delete_kk();