Trigger in Postgre

I am recently using postgre, the documentation is very helpful. But I stil need to post this trigger syntax for my documentation In postgre, you have to make a procedure (function) first before the trigger. Then in your trigger, you call that procedure. Here’s the example:

CREATE OR REPLACE FUNCTION air.shipmentno_update()
RETURNS “trigger” AS
‘
BEGIN
IF(NEW.shipment_id!=OLD.shipment_id) then

UPDATE air.a_log_transaksi SET transaksi_id = NEW.shipment_id
WHERE  status_aktif=1 and transaksi_id = OLD.shipment_id;
END IF;
RETURN new;
END;
‘
LANGUAGE ‘plpgsql’ VOLATILE;
CREATE TRIGGER shipmentno_trigger
AFTER UPDATE
ON air.a_shipment
FOR EACH ROW
EXECUTE PROCEDURE air.shipmentno_update();

The function of trigger above is to update the shipment_id in the a_log_transaksi table when the shipment_id in the table shipment changes.

An example of a procedure which had a loop through query result :

CREATE OR REPLACE FUNCTION air.shipmentno_delete()
RETURNS “trigger” AS
‘
DECLARE
cargo RECORD;

BEGIN

UPDATE air.a_log_transaksi SET status_aktif=2
WHERE transaksi_id = OLD.shipment_id;

FOR cargo IN SELECT * FROM air.a_cargo where shipment_id=OLD.shipment_id
LOOP
UPDATE air.a_log_transaksi SET status_aktif=2 WHERE  tabel=a_cargo and transaksi_id =cargo.cargo_id;
END LOOP;

RETURN OLD;
END;
‘
LANGUAGE ‘plpgsql’ VOLATILE;

and the trigger is :
CREATE TRIGGER shipmentnodelete_trigger
AFTER DELETE
ON air.a_shipment
FOR EACH ROW
EXECUTE PROCEDURE air.shipmentno_delete();

Well, that’s all for know. Hope this post help you in any kind of way, at least it would help myself in the future:)