1

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

1
  • 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?
    – Akina
    Dec 15, 2022 at 18:48

1 Answer 1

2

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.