--po_info為主表,po_item為從表,po_info中有更新欄位lastupdatetime,兩張表關聯欄位po_id
---------------po_id用觸發器監控表的更新,用lastupdatetime記錄時間------------------
--建立觸發器過程--
CREATE OR REPLACE FUNCTION po_info()
RETURNS trigger AS
$BODY$
BEGIN
If (TG_OP = 'UPDATE') THEN
If NEW.lastupdatetime = OLD.lastupdatetime Then
update po_info set lastupdatetime = now() where po_id=new.po_id ;
END IF;
END IF;
return null;
end;
$BODY$ LANGUAGE plpgsql
--建立觸發器--
CREATE TRIGGER tr_po_info
AFTER update ON po_info
FOR EACH ROW EXECUTE PROCEDURE po_info();
---------------po_item用觸發器監控表的更新,更改po_id的lastupdatetime時間------------------
--建立觸發器過程--
CREATE OR REPLACE FUNCTION po_item()
RETURNS trigger AS
$BODY$
BEGIN
If (TG_OP = 'UPDATE') THEN
If (NEW != OLD) Then
update po_info p1 set lastupdatetime = now() where p1.po_id=new.po_id ;
END IF;
END IF;
return null;
end;
$BODY$ LANGUAGE plpgsql
--建立觸發器--
CREATE TRIGGER tr_po_item
AFTER update ON po_item
FOR EACH ROW EXECUTE PROCEDURE po_item();