How to move the only updated row to the hist_table postgres?

Question

I Created the next Trigger to move the data from a main table to an historical table in case of an update or insert.

CREATE OR REPLACE FUNCTION process_employee_hist() RETURNS TRIGGER AS $employee_hist$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            INSERT INTO employee_hist SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO employee_hist SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL;
    END;
$employee_hist$ LANGUAGE plpgsql;

CREATE TRIGGER employee_hist
AFTER INSERT OR UPDATE ON employee
    FOR EACH ROW EXECUTE PROCEDURE process_employee_hist();

This Trigger moves all data from the main table to the historical. I need only to move the updated one to the historical table.

Rupert · Accepted Answer · 2022-05-19 12:04:00Z

0

Try:

CREATE OR REPLACE FUNCTION process_employee_hist() RETURNS TRIGGER AS $employee_hist$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO employee_hist SELECT 'U', now(), user, NEW.*;
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$employee_hist$ LANGUAGE plpgsql;

CREATE TRIGGER employee_hist
AFTER UPDATE ON employee
    FOR EACH ROW EXECUTE PROCEDURE process_employee_hist();

answered May 19, 2022 at 12:04

Rupert

1501 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to move the only updated row to the hist_table postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related