timestamp in log table after update in another table - Mailing list pgsql-novice

Hi,

I have three tables which are frequently updated with new data. If any of the tables are updated I need an automated way to create a timestamp (indicating which unit_id has been updated/when) in a separate log table. The tables have the following rows

Data tables (db_raw_data1,2,3):

aquis_data | aquis_time | unit_id |  ... + a number of attributes specific to each table.

Log table:

unit_id | timestamp|      Unit_id is a primary key and = to unit_id in the raw data table

My approach was to use a trigger in the data tables which would feed a timestamp to the corresponding unit_id entry in the log table. I have been thinking of a function along the following lines (which obviously hasn't been successful):

I have googled this issue but haven't found a reference to updating a timestamp in another table. Any suggestions?


CREATE OR REPLACE FUNCTION public."update_log_tbl"()
RETURNS TRIGGER AS
$$
BEGIN

UPDATE data.log
SET    last_update = timestamp
WHERE  db_raw_data.unit_id = log.unit_id;

end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public."log_tbl"()
  OWNER TO postgres;