How to move the only updated row to the hist

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.

CodePudding user response：

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