(PostgreSQL)make table触发功能将更改后的行的ID值插入到日志表中，以进行自定义复制 [英] (Postgresql) make table trigger function insert ID value of changed row into log table, for custom replication

问题描述

对于Postgresql(最终是Sybase ADS)，我有一些使用自制触发器进行复制的表.触发器具有应记录有关INSERT UPDATE DELETE更改的信息的功能，其中包括I/U/D操作和更改的行的ID.我正在使用带有名字列和自动递增ID的测试表来测试双向复制.

For Postgresql---and eventually Sybase ADS---I have tables that will replicate using homemade triggers. The triggers have a function which should record info for INSERT UPDATE DELETE changes which include the I/U/D operation and the ID of the row that changed. I am using test tables with a first name column and auto incrementing ID to test bidirectional replication.

如果我在NAME表中进行插入(例如添加自动ID为"1"的"JOHN")，我是否可以将该ID值甚至"JOHN"插入日志表中?我正在查看OLD.ID或NEW.ID选项，但不知道那是如何工作的.我的触发器/功能代码是:

If I make an insert to the NAME table such as adding 'JOHN' with an autoID of '1', am I able to insert that ID value and even possibly 'JOHN' into a log table? I am looking at the OLD.ID or NEW.ID options but I don't know if that's how it can work. My trigger/function code is:

    CREATE OR REPLACE FUNCTION Insert() RETURNS TRIGGER AS $ReplInsert$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('DELETE', OLD.ID, OLD.NAME);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('UPDATE', NEW.ID ,NEW.NAME);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('UPDATE', NEW.ID , NEW.NAME);
            RETURN NEW;
        END IF;
        RETURN NULL; 
    END;
$ReplInsert$ LANGUAGE plpgsql;

CREATE TRIGGER logTrg
AFTER INSERT OR UPDATE OR DELETE ON "FIRST_NAME"
    FOR EACH ROW EXECUTE PROCEDURE Insert();

这最终将使用触发器进行双向复制.感谢您的任何考虑或指导.我将根据需要进行编辑.

This is ultimately to make bidirectional replication using triggers. Thank you for any consideration or guidance. I will edit as needed.

推荐答案

我能够通过在触发器中将AFTER更改为BEFORE来解决此问题，我一开始认为这是行不通的，或者:

I was able to resolve this by changing AFTER to BEFORE in the trigger, which I believed at first wouldn't work, or:

    CREATE OR REPLACE FUNCTION Insert() RETURNS TRIGGER AS $ReplInsert$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('DELETE', OLD.ID, OLD.NAME);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('UPDATE', NEW.ID ,NEW.NAME);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO "log" ("Operation","ID","NAME")
    VALUES ('UPDATE', NEW.ID , NEW.NAME);
            RETURN NEW;
        END IF;
        RETURN NULL; 
    END;
$ReplInsert$ LANGUAGE plpgsql;

CREATE TRIGGER logTrg
BEFORE INSERT OR UPDATE OR DELETE ON "FIRST_NAME"
    FOR EACH ROW EXECUTE PROCEDURE Insert();

这篇关于(PostgreSQL)make table触发功能将更改后的行的ID值插入到日志表中，以进行自定义复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！