Hi,
You can use NEW record in trigger function e.g
CREATE OR REPLACE FUNCTION public."update_log_tbl"()
RETURNS TRIGGER AS
$$
BEGIN
UPDATE data.log
SET last_update = now()
WHERE unit_id = NEW.unit_id;
RETURN NEW;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
My assumptions are:
- trigger will be fired after INSERT and/or UPDATE, because for DELETE there is no NEW record, for DELETE should be modified;
- This is row level trigger (not statement);
- Trigger(s) will be defined on tables db_raw_data;
- You need log transaction start time not an time when record was modified during query execution. This is the difference between now() and clock_timestamp() functions;
- SECURITY INVOKER is intended - user who inserts/modify data in db_raw_data has to have privilages to modify data in data.log table (is is secure?).
NEW record represents inserted or modified record (new version) - is NULL for DELETE
OLD record represents previous (existing) version of modified record - is NULL for INSERT.
Regards,
Bartek
2012/2/10 Falk Grossmann
<fgrossmann@gmail.com> 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;