update current row doesn't trigger attached function

Question

To update a column in PostgreSQL using the function which is invoked by a trigger on each row add/update-

Steps I followed:

First, let's add the field "line_updated_dt_tm" to the table of invoice lines:

ALTER TABLE tb_lines_invoice
ADD line_updated_dt_tm VARCHAR(20);

Next, let's create the function to populate the "line_updated_dt_tm" field with the current system date and time in the specified format:

CREATE FUNCTION fn_line_inserted()
RETURNS TRIGGER AS $$
BEGIN
 NEW.line_updated_dt_tm := to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MI');
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Finally, let's create the trigger that will execute the function whenever a new record is inserted or an existing one is modified:

CREATE TRIGGER tg_line_inserted
AFTER INSERT OR UPDATE ON tb_lines_invoice
FOR EACH ROW
EXECUTE PROCEDURE fn_line_inserted();

This should add the current system date and time in the specified format to the "line_updated_dt_tm" field whenever a new record is inserted or an existing one is modified.

Issue:

New field i.e line_updated_dt_tm not updated on row add or update operation

• Akina: AFTER INSERT trigger is fired after the insertion - i.e. when the values are already saved into the table. Do you want the trigger to save the values one more time?

Answer 1

Use a BEFORE trigger

CREATE TRIGGER tg_line_inserted
BEFORE INSERT OR UPDATE ON tb_lines_invoice
FOR EACH ROW
EXECUTE PROCEDURE fn_line_inserted();