如何将唯一更新的行移至Hist_table Postgres？

发布于 2025-01-30 06:02:05 字数 673 浏览 5 评论 0原文

在更新或插入的情况下，我创建了下一个将数据从主表移动到历史表的触发器。

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

这个触发器将所有数据从主表移动到历史。我只需要将更新的一个移至历史表。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪推晚风 2025-02-06 06:02:05

尝试：

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

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

回复收藏 0 原文

~没有更多了~